WebmasterSite.net: PHP scripts to enable your creativity
WSN Links PHP Directory Software
PHP Scripts Webmaster Links Support Forums

Register | Forgot Password
WSN Support » WSN Links » Database queries » Database queries

Database queries

Version:
printPrint


Database queries
fresco
Forum Regular

Usergroup: Customer
Joined: Feb 19, 2003
Total Topics: 23
Total Posts: 106
Posted 10/13/04 - 04:00 PM:
quote post
#1
Hi I have edited templates to look exactly as my old 2.55 install it generated 44 queries. removed a few switches but still have 28 queries to generate index page...

on 2.55 i had 8!

8 > 28 quite a lot of difference with the same configuration of the templates on my main index...

Do i miss something?

Is there any way to reduce number of queries?
Paul
Administrator
Avatar

Usergroup: Administrator
Joined: Dec 21, 2001
Location: Northern California
Total Topics: 57
Total Posts: 6339
Posted 10/14/04 - 06:17 AM:
quote post
#2
Number of queries does not equate to runtime. Queries are not created equal. Many methods of reducing queries cause the page rendering time to increase.

The runtime may have increased a little I think, mainly due to a storing objects in an array method that doesn't work in PHP 5 and a fix to a toplists problem, but it has not increased that significantly. On http://links.webmastersite.net for example I get:
"30 total queries
This page was created in 0.40084099769592 seconds"

That's not bad at all, I don't remember it ever being much below 0.4. It goes up to 0.78 in the biggest category, but that's same as always -- and note that in that category it has only 29 queries, so with one less query it takes twice as long to load.

About 75% of the page render, by my estimation, takes place in the replacement procedures where PHP parses up the templates replacing bits with the already loaded data. You will notice that if you load the exact same page with a custom template that just has plain text (no link/cat/etc data) in it then it loads many times faster -- despite having the exact same number of queries.

Edited by Paul on 10/14/04 - 06:22 AM
"Do things you love doing, because then it ain't work. Don't do something you don't really enjoy, because you're never going to work hard enough at it." - Bob Young, founder of Red Hat
fresco
Forum Regular

Usergroup: Customer
Joined: Feb 19, 2003
Total Topics: 23
Total Posts: 106
Posted 10/14/04 - 09:14 AM:
quote post
#3
it loads my pages between 1.5 to 4 seconds, that gets me worried a little.
fresco
Forum Regular

Usergroup: Customer
Joined: Feb 19, 2003
Total Topics: 23
Total Posts: 106
Posted 10/14/04 - 09:27 AM:
quote post
#4
what happened to my "bug" post?

I can not access memberlist at all it always gives this:

"Timestamp is not numeric, it is 1228354260. Please report this bug."

also this is what it does during the peak hours:

26 total queries
This page was created in 9.6695680618286 seconds

the 2.55

was never over 1.9 seconds

fresco
Forum Regular

Usergroup: Customer
Joined: Feb 19, 2003
Total Topics: 23
Total Posts: 106
Posted 10/14/04 - 10:11 AM:
quote post
#5
something must be terribly wrong with my install - how can i troubleshot?

26 total queries
This page was created in 27.388688087463 seconds
Paul
Administrator
Avatar

Usergroup: Administrator
Joined: Dec 21, 2001
Location: Northern California
Total Topics: 57
Total Posts: 6339
Posted 10/14/04 - 01:06 PM:
quote post
#6
what happened to my "bug" post?

I renamed it something that at least gave some slight clue of what it was about, so that it could be found. Please title topics with something that addresses their content.

it loads my pages between 1.5 to 4 seconds, that gets me worried a little.

Unless you're on an extra slow server, that's not normal.

27.388688087463 seconds

Possible on index.php if you have a timed action like a large backup going on.

something must be terribly wrong with my install

Have you checked if you get similar behavior on a blank new install?
"Do things you love doing, because then it ain't work. Don't do something you don't really enjoy, because you're never going to work hard enough at it." - Bob Young, founder of Red Hat
fresco
Forum Regular

Usergroup: Customer
Joined: Feb 19, 2003
Total Topics: 23
Total Posts: 106
Posted 10/14/04 - 05:12 PM:
quote post
#7
i just uploaded old 2.55 files and it takes 1 - 1.7 to create any page

live one - with 312 never goes below 3.0 as you have said it is not normal.

extra slow server - what does this mean exactly?
fresco
Forum Regular

Usergroup: Customer
Joined: Feb 19, 2003
Total Topics: 23
Total Posts: 106
Posted 10/15/04 - 05:15 AM:
quote post
#8
I have turned the debug on and it listed queries and one line made me think that perhaps it could be a reason for slow times

Performing query: SELECT id FROM wsnlinks_categories WHERE type='' ;

in my settings categories have type which is "regular"

should the line above reflect that like it does for links? or it only has anything to do with "mix link type" set to 'no'?
Paul
Administrator
Avatar

Usergroup: Administrator
Joined: Dec 21, 2001
Location: Northern California
Total Topics: 57
Total Posts: 6339
Posted 10/16/04 - 03:28 AM:
quote post
#9
Such a query should be very quick, especially because it should yield no results if you don't have any categories with blank types. There is however no reason for it to exist. I guess you probably do have all the types blank, and you're just reading the 'regular' it defaults to showing.
"Do things you love doing, because then it ain't work. Don't do something you don't really enjoy, because you're never going to work hard enough at it." - Bob Young, founder of Red Hat
fresco
Forum Regular

Usergroup: Customer
Joined: Feb 19, 2003
Total Topics: 23
Total Posts: 106
Posted 10/16/04 - 08:19 AM:
quote post
#10
Paul wrote:
Such a query should be very quick, especially because it should yield no results if you don't have any categories with blank types. There is however no reason for it to exist. I guess you probably do have all the types blank, and you're just reading the 'regular' it defaults to showing.

does it mean everything with it is fine or i need to fix something?
Paul
Administrator
Avatar

Usergroup: Administrator
Joined: Dec 21, 2001
Location: Northern California
Total Topics: 57
Total Posts: 6339
Posted 10/17/04 - 03:59 PM:
quote post
#11
It means it's fine unless it doesn't work, in which case it isn't fine.

I recall you had a lot of toplists. Those do take longer to load now than in 2.5, due to a fix to make it grab extras in case of categories which the viewer doesn't have permissions to view links from.
"Do things you love doing, because then it ain't work. Don't do something you don't really enjoy, because you're never going to work hard enough at it." - Bob Young, founder of Red Hat
fresco
Forum Regular

Usergroup: Customer
Joined: Feb 19, 2003
Total Topics: 23
Total Posts: 106
Posted 10/17/04 - 05:24 PM:
quote post
#12
so it would help if I am to generate java to store the toplists?

i have removed toplist (i only heve one per category but it is category specific toplist) and the time went down 20-30% however it is still much higher than yours 1.9-2.7

any other reasons it might be slow?

i also tried to set sponsored links those give high execution time spike - should it be like that?

i will move the site to the high-power server but i would like to make sure i am moving - errors.

Edited by fresco on 10/18/04 - 09:52 AM. Reason: more stuff
Paul
Administrator
Avatar

Usergroup: Administrator
Joined: Dec 21, 2001
Location: Northern California
Total Topics: 57
Total Posts: 6339
Posted 10/18/04 - 09:33 PM:
quote post
#13
so it would help if I am to generate java to store the toplists?

It would lower page load time normally, but every 12 hours it'd end up being much slower when it has to generate the javascript. With several toplists at the rates you're getting that could start leading to php timeouts before it can complete.

Having sponsor links should only add one query, which shouldn't add much load time.

Are you working with a remote database server? With remote databases queries end up taking much longer to execute than with a local database.
"Do things you love doing, because then it ain't work. Don't do something you don't really enjoy, because you're never going to work hard enough at it." - Bob Young, founder of Red Hat
fresco
Forum Regular

Usergroup: Customer
Joined: Feb 19, 2003
Total Topics: 23
Total Posts: 106
Posted 10/20/04 - 10:08 AM:
quote post
#14
no database is on localhost (i have dedicated server but it is not too fast and gets over 0.5 mil calls/hour).

i will be moving the site on its ovn box but do not want to move errors (if any).


Hi Paul,

I am trying to move the sita and database backup persistently creates 12 tables, however in exsiting database I have 13 tables, the missing table is "Languages" looks like this table does not exist in 312 i could not find it in 312 setup.sql...?

Could it be the cause for database querees being slow - a left over table?

Edited by fresco on 10/23/04 - 01:03 AM. Reason: new info
Search thread for
Download thread as


You don't have permission to post.

Please login or register.

© 2008

   
 
© 2008 Paul Knierim. All rights reserved.