yii multiple database queries or array

Question

We are developing an application with a complicated UI in yii. I am not the programmer but noticed that the application page load times seemed slow.

I turned on MySQL query logging and found that there were many instances of repeated queries such as "select x,y,z from table where id = 1", "select x,y,z from table where id = 2", "select x,y,z from table where id = 3". Replacing these with a single "select x,y,z from table where id in (1,2,3)" takes virtually the same duration as running one of the single queries. In aggregate doing so would shave an order of magnitude off the load time. Looks to me like a query is happening within a loop rather than a where clause being constructed first, all data retrieved to an array and then looping through the array.

When I pointed this out to the programming team, I was told that in PHP looping through an array is as slow as doing separate SQL calls. I find this hard to believe given my experience with other programming languages, it looks like DAO would do this easily. The other issue appears to be complex models. Any advice appreciated.

Community · Accepted Answer · 2017-05-23 12:05:21Z

0

It depends. If your array wont be serialize for session storage for future calls, i think it is faster to query an array on memory than making multiple calls to the db. But i also think this is not practical unless your really going to use all the data, i mean, fetch a huge number of rows and you wont be displaying it all to the user. And i also think this not helpul either as your wasting the power of your db see Massive PHP array vs MySQL Database?. Im not sure if im in the correct context here but my advice would be that somehow try to build your query correctly first so you query your db just once, and if it is too much information use pagination and work with just a fixed number of rows at each time. Hope it helps...

edited May 23, 2017 at 12:05

CommunityBot

11 silver badge

answered Mar 19, 2014 at 22:06

roboli

1,47820 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3438020 Over a year ago

It looks to me like in most cases 10 - 20 single calls are being made to the db when 1 more difficult to code one would suffice and have the same duration as any one of the single calls. So the question is whether it is worth the effort to do the coding. We're talking 10 -20 rows that are needed by an html page, not getting a bunch of data that isn't going to be immediately used. Each page takes a number of such calls (in aggregate maybe 100).

Collectives™ on Stack Overflow

yii multiple database queries or array

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related