Tilted Forum Project Discussion Community - View Single Post

twister002 · 04-16-2004, 10:12 PM

So you never specified what RDBMS you are using but this line

Code:

dbo.Meetings.MeetingID

tells me that it's probably SQL server.

One thing you can do is create a view based on the query and select from that. That can speed up execution some. Create a view based on the join tables and index the view. Since you want to include aggregate values in your result set you can't use an indexed view for your final query.

Since you want to include the types based on user input you probably won't be able to use a stored procedure. Stored Procedures can speed things up some because you get the benefit of a pre-compiled (meaning determined) execution path. You *CAN* add columns dynamically, but that kind of programming in T-SQL makes my teeth hurt so I'm not going to suggest that. I'll only give a small piece of advice, select your final result set into a table variable rather than a temp table if you are using SQL Server 2000. Table variables are much more efficient than temp tables in SQL 2K.

The big thing you need to do, and didn't mention, is starting up the SQL profiler and running a trace against the database when you run that query. That'll tell you if it's efficient or not. Start up Query Analyzer, run your query and look at the execution path. That'll tell you where the query takes the most time while it's executing. Make sure your tables are properly indexed.

FYI I run almost exactly the same kind of query against a patient database, counting the number of labs, etc.. that each one has, on a weekly basis and I've got about a quarter of a million rows in one of the tables in my query and there are 7 tables that I hit. It takes a little under 20 seconds to run.

04-16-2004, 10:12 PM	#5 (permalink)
twister002 Crazy	So you never specified what RDBMS you are using but this line Code: dbo.Meetings.MeetingID tells me that it's probably SQL server. One thing you can do is create a view based on the query and select from that. That can speed up execution some. Create a view based on the join tables and index the view. Since you want to include aggregate values in your result set you can't use an indexed view for your final query. Since you want to include the types based on user input you probably won't be able to use a stored procedure. Stored Procedures can speed things up some because you get the benefit of a pre-compiled (meaning determined) execution path. You CAN add columns dynamically, but that kind of programming in T-SQL makes my teeth hurt so I'm not going to suggest that. I'll only give a small piece of advice, select your final result set into a table variable rather than a temp table if you are using SQL Server 2000. Table variables are much more efficient than temp tables in SQL 2K. The big thing you need to do, and didn't mention, is starting up the SQL profiler and running a trace against the database when you run that query. That'll tell you if it's efficient or not. Start up Query Analyzer, run your query and look at the execution path. That'll tell you where the query takes the most time while it's executing. Make sure your tables are properly indexed. FYI I run almost exactly the same kind of query against a patient database, counting the number of labs, etc.. that each one has, on a weekly basis and I've got about a quarter of a million rows in one of the tables in my query and there are 7 tables that I hit. It takes a little under 20 seconds to run.