Complicated SQL Question.
Little background info...
1 Web Server running Win 2003, IIS 6.0, .NET Framework 1.1 SDK
1 SQL Server running Microsoft SQL Server 2000
Here is the problem. I have a massive query that runs from the Web Server as a SQLCommand on a .NET VB page using the SQL Adapter. For the most part it will time out. I have never seen it timeout from a Query Analyzer Standpoint, but I have seen it take well over 2 hours and I will just kill it. Now here's the odd part, I've also see the query take under 4 minutes to complete, both on Query Analyzer and Querying it from the Web Server. So I'm guessing that there is something that I am missing. I join on mulitple tables, and I alias a table twice that has just over 7 million rows in it and join on it, for some reason I cannot find the reason behind the query running fine one day then not running at all the next. I have 4 indexes on the table, one is a clustered index that takes into account the most joined on fields and the fileds that are used in any WHERE clause, it has the pad index selected, and a fill factor of 50. I know without looking at the query it's hard to really see what is going on, but is there any thing that I could look for that may help performance. I've tried combonations of udf's, views but nothing really seems to give me a solid and constant result. It just seems to work fine one day and not the next, I also have a DTS package the forces a reindex of the table, with the fill factor matching. Is there anything that I can do? One more thing to note is that when the performace was great I had turned the reindexing DTS off for about two days and it just seems to have started working fine, then when I turned indexing back on it seems to stop working. I'm thinking this is just a turn of chance, but maybe not. So what I guess I'm looking for is some tips on how to actually see what is going on when I run the query, and why the query might run in 4 minutes sometimes, but take upto 2 hours the next.
[2053 byte] By [
jrs79] at [2007-11-19 18:57:05]
# 1 Re: Complicated SQL Question.
To set timeout period - there is a element in the connection string that you can set. It basically depends on the db schema and the query that you are using. The joins might not be correct, indexed columns would be wrongly put. The where clauses can be in-efficiently clubbed.. who knows.. it's hard to guess that way.
What you can do is try getting to know the execution plan/path of the query. In sybase there is a sp_showplan that helps analyze this. Put the query in a stored procedure to see if that helps. sp_showplan might not be available with ms sql server, i guess, but then you can use the profiler? Regards.
# 2 Re: Complicated SQL Question.
Just a few idea's.
Is it strictly necessary to reindex the table each time?
Also, is the fillfactor of 50 required? This can cause performance issue when reading the table/s.
SQL online books states the following
The fill factor option is provided for fine-tuning performance. However, the server-wide default fill factor, specified using the sp_configure system stored procedure, is the best choice in the majority of situations.
Note Even for an application oriented for many insert and update operations, the number of database reads typically outnumber database writes by a factor of 5 to 10. Therefore, specifying a fill factor other than the default can degrade database read performance by an amount inversely proportional to the fill factor setting. For example, a fill factor value of 50 percent can cause database read performance to degrade by two times.
It is useful to set the fill factor option to another value only when a new index is created on a table with existing data, and then only when future changes in that data can be accurately predicted.
# 3 Re: Complicated SQL Question.
or some reason I cannot find the reason behind the query running fine one day then not running at all the next.
Check also if there are no open transactions/ dedlocks etc. (See it in "Currect activity" in Enterprice manager while running query ie. blocking/blokee).
Update statistics too!
it has the pad index selected, and a fill factor of 50
fill factor is usefull only if U want to speed up insert or updates (by lowering page splits operations required) degradating the same time query performance (because making empty reserved sapce U increase no. of pages which have to be read from HD for the same sample of data).
Hope that it helps.
Krzemo at 2007-11-9 13:45:13 >
