very fast queries 1

[jepatte]

I have a site that has voter information for a state. There are over 2.5 million records in the main table with 50 fields. This site allows users to choose many different filters to get counts. ie. Race, Age, Gender, Vote history, etc. No matter what my indexes are, it still takes anywhere from 3 - 60 seconds to return results.

Here's the thing, there is another site like mine that is even more complex with many more fields, and they can return any count no matter how complex in less than one second. Can someone tell me how this is possible? Do you think they really have it reading all 2.5 million records and returning results that fast.

My server has SQL 2000 and 4 Gb ram on a Windows 2003 box. I'm not sure what this other site is using.

 

[chrissie1]

Nah, Denis has no idea what he si talking about ;-)

Christiaan Baes
Belgium

My Blog

 

[gmmastros]

Think for a moment about this table.

In your original question, you wanted to know how to speed up reports. Specifically, you want to be able to apply filters on the data and report on counts.

Ex:

[tt]Favorite Food Count
------------- -----
Pizza 1,800,200
Hot Dogs 125,020[/tt]

No where, in the results, will you be displaying detailed information about any individual, right? You'll never show address or email information. By removing this information to another table, you will make your main data table be a lot narrower, allowing more records to fit in to a page. This will effectively speed up your queries.

The more rows you have, the more you will notice the effect of rows/page. With small tables, it won't really matter too much, but it does with larger tables.

But... getting back to the original question... how do I make my queries faster....

Increasing the number of rows per page is one method. Constructing your query is another method. Is it possible to show your query. There are many aspects of a query that will affect performance. Of utmost importance is INDEXES. Without proper indexes, your queries will be slow. Secondly, its important that your indexes are used effectively. Your filter conditions should be 'sargable' (Hint: do a google search on that term).

I suggest you post your query so that we can take a look at it.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[CaptainD]

gmastro:

Have a star.

I have never heard of "Sargable" and after reading

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbugen9/00000463.htm

I know of several queries that are not "Sargable" and can be improved.

Another thanks for the explaining pages.

D

 

[SQLSister]

In addition to the performance issue, I will point out that people have multiple addresses, phones and emails which is another reason why they should each be in a separate table.

Her is some reading on basic relational database design

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

While we are on the subject you will also want to avoid cursors and loops and dynamic SQl if you are interested in fast performance. And never use select * unless you are sure you need evey one of those fields. The fewer pieces of data sent across the network the faster you get results back. If you have a join, by definition, select * will return extra columns you don't need as at least two f the columns (the join fileds) will have the same data.

"NOTHING is more important in a database than integrity." ESquared

 

[jepatte]

I appreciate everyone's help with this. I had a few unnecessary fields in the big table and the total bytes I had for the rows were at 1050. After I removed some of the columns, I got the bytes down to 550 and I can see a great improvement already in speed. I just assumed that as long as I only selected the fields I needed in my sql statement, I would be ok, but now I see that just the amount of fields in the table affect speed a lot.

 

[ESquared]

Will it take you longer to find your groceries in a store that has 500 products per shelf, or only 1 product per shelf?

... the second grocery store is going to have wayyyy more shelves than the first, and you'll spend an awful lot of time trucking around to different shelves. It's much better to group related products together and make each one take less space, then get several of them in one pass through an aisle ...