mjbkm (9/25/2011)
Well, I have a huge table. Going on 3,000,000+ records with a aspx web front where users actively query this table with around 10 different parameters. Just completed SQL/aspx classes and newbie. I have been an Access/vb programmer for 10+ years. So my job is to figure out how to handle a large table that needs this type of activity and how to do it quickly. So far I am pretty successful... I have the largest possible query running in 18 seconds. However, I think that is still pretty slow.... Been working on:Limit columns returned
Primary Key
Indexes on where clause fields
avoiding case statements.
grouping, joins, & sort last
Found this option. And it worked pretty good -I mean looking forward, splitting this table up by year. Because the user always queries a specific year at a time. Never a range of years. So I was going to use a table name as a parameter to pick what table(year) to query by. Making the number of tables I am working with less.
However, if you have any ideas that would be great. Thank you for your help.
Do you have the Standard Edition or the Enterprise Edition of SQL Server? And, just so you know... CASE functions in SELECT list aren't so bad.
And Gail is right... if your indexes are good, you shouldn't have a problem with SELECTs although there are some maintenance aspects that provide some payoff insofar as rebuilding indexes goes if your "manual" partitioning (which can be simplified with some intelligent scripting) is based on a temporal column.
In either case, you shouldn't have to call out a specific table name for each year that you want to process. Lookup "Table Partitioning" if you have the Enterprise Edition and "Partitioned Views" if you have the Standard Edition.
--Jeff Moden
Change is inevitable... Change for the better is not.