Performane Problem
Jan 15, 2007
Hello.
Your help is very much appreciated.
I have SQL SERVER 7.0 running about 10 databases.
Only 1 database is problematic.
When running quries on the two largest tables on this database(one has 2.4 million records, the other one has 3 million records), it stops responding/times out, even if the query is run on the server itself.
We do have another table(in other database, on the same server) with 15 million records, which runs slow but keeps on working.
(in the past,rebooting every few days solved the probelm)
It might be indexing or maybe table/record locks?
What is your recommendation regarding how to find where (and later, what) the problem is?
Thank you
Cheers
Roy
View 3 Replies
Jul 23, 2005
HELP!!!I am trying to fine tune or rewrite my SELECT statement which has acombination of SUM and CASE statements. The values are accurate, butthe query is slow.BUSINESS RULE=============1. Add up Count1 when FIELD_1 has a value and FIELD_2 is NULL, or bothhave a value.2. Add up Count2 when FIELD_2 has a value and FIELD_1 is NULL.4. TotalCount = Count1 + Count2 -- (Below, basically had to reuse theSQL from both Count1 and Count2)3. Add a NoneCount when both FIELD_1 and FIELD_2 are NULL.SQL Code========SELECTSUM(CASEWHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR (FIELD_1 IS NOTNULL AND FIELD_2 IS NOT NULL))THEN 1ELSE 0END) AS Count1 ,SUM(CASEWHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)THEN 1ELSE 0END) AS Count2,SUM(CASEWHEN (FIELD_1 IS NULL AND FIELD_2 IS NOT NULL)THEN 1ELSE (CASE WHEN ((FIELD_1 IS NOT NULL AND FIELD_2 IS NULL) OR FIELD_1IS NOT NULL AND FIELD_2 IS NOT NULL) THEN 1 ELSE 0 END)END) AS Total_Count,SUM(CASEWHEN ( FIELD_1 IS NULL AND FIELD_2 IS NULL)THEN 1ELSE 0END) AS None_Count,FROMTABLE_1
View 1 Replies
View Related
Apr 11, 2007
SQL2K
SP4
Howdy all. I have done a fair amount of performance tuning and deadlock resolving in my day, but never under these circumstances. Here is the scenario:
A sproc written in Dynamic SQL (with a dynamic WHERE clause I should add) queries a View. This View is joined to several more Views, that are joined to several more Views, that are joined to several more Views, that are.......
So, what I've been having to do to find out if my column is indexed or not is read View1, then View2, and so on and so forth until I figure out what the table really is.
This is extremely time consuming. I can actually have 10 - 20 joins by the time all is said and done.
Now, I am aware that Dynamic SQL is bad, and I'm also aware that 10 - 20 joins is bad. But that doesn't solve anything for me. Can anyone provide any ideas on how to troublshoot perfomance issues and deadlocks in this scenario?
TIA, ChrisR
View 9 Replies
View Related
