[thelist] Query Execution Time Excel vs. SRS/SQL Server

[Richard Bennett]

On Friday 28 April 2006 17:16, Rob Smith wrote:
> Is there a reason why Excel can churn out a pretty complex query in a
> matter of seconds, when the same query run in SRS or SQL Server 2005
> would take several minutes?

Excel is specialized in doing calculations, so does this well. There are many 
things is doesn't do so well.
It is limited to x rows (65000 i think). 
Flatfiles are often faster than database, but offer less features.

With carefull planning, normalization and design tweaks a database can usually 
return any query in a matter of seconds.
For instance, you have a log-table of 10mil records, and you want to group 
yesterdays logs by username.
Just grouping the whole thing by date and username will be very slow.
Selecting all records with date=yesterday, and grouping that by username will 
be ok if the table is well indexed, but sometimes it is fastest to select 
yesterdays records into a temp table, and do your grouping on that. This way 
you also free-up the main table for others to access it. This is specially 
true if you're going to be doing complex joins on yesterdays data.

The first thing to check is your indexes. Basically each table should have a 
(unique of course) primary index, and any column you use to query by should 
also be indexed.
  
Also tables become 'un-optimized' over time, running some nightly optimise 
routines keep the database nice and fast.

Then there are some code tweaks... using WHERE VAL='hi' is faster than WHERE 
VAL LIKE 'hi' ,  if you don't need any wildcars... CHAR is faster than 
VARCHAR... etc.

Also check your db has enough ram and diskspace. 

HTH

Richard