Union All - gets slow performance and timeout expired

Question

Union All - gets slow performance and timeout expired

SQL Galaxy

One Orange Chip

Points: 29472
More actions
June 23, 2011 at 1:00 am

#389825

Hi,
Please give me good solution as below SQL statement, application gets error - Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Recently upgrade to SQL 2008 from SQL 2000, Error facing after completion upgrade process.
1. view name - Tel_Txt_Li_St_ANOMALY - total 6 records
SELECT SITE, PLANT, A AS EXT, D AS NAME, E AS LOCATION
FROM dbo.TEL_TXT_LI_ST AS st
WHERE (NOT EXISTS
(SELECT Ext
FROM dbo.Tel_Bincard_Detail AS bin
WHERE (Remove_Date IS NULL) AND (Site = st.SITE) AND (Ext = st.A)))
2. view name - Tel_Bincard_Detail_ANOMALY - 20366 records
SELECT Site, 'IT' AS PLANT, Ext, EName AS NAME, Location
FROM dbo.Tel_Bincard_Detail AS bin
WHERE (Remove_Date IS NULL) AND (NOT EXISTS
(SELECT bin.Ext
FROM dbo.TEL_TXT_LI_ST AS st
WHERE (SITE = bin.Site) AND (A = bin.Ext)))
3. Application using final statement - this query taking and gets output 59 seconds. ( here merge with above both views)
SELECT 'Bincard' ANOMALY_IN, count(1) TOTAL FROM
Tel_Txt_Li_St_ANOMALY where site='JMN'
and PLANT='IT'
UNION ALL
SELECT 'List Station' ANOMALY_IN, count(1) TOTAL FROM
Tel_Bincard_Detail_ANOMALY where site='JMN' and PLANT='IT'
Thanks
ananda

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

chandan_jha18 SSChampion Points: 11330 More actions · Answer 1

You need to include table and index schema details as well as the execution plan for any query. This will help all the experts to analyze and suggest you some solution.

BTW, make sure you update statistics on your database after migration.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

yes, after migration i have done as below

1. dbcc checkdb with data purity

2. Rebuild index

3. update statistics both all existing statistices and column level

thanks

chandan_jha18 SSChampion Points: 11330 More actions · Answer 3

ananda.murugesan (6/23/2011)
yes, after migration i have done as below
1. dbcc checkdb with data purity
2. Rebuild index
3. update statistics both all existing statistices and column level
thanks

If you rebuild all the indexes, no need to update the statistics then. It will be done automatically.

Where is the schema details and execution plans for your queries. Without them, it is not possible to give a fair judgement.

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

execution plan for query

SQL Server Execution Times:

CPU time = 43313 ms, elapsed time = 45799 ms.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 5

ok.. I think i will ask this 3rd and last time:-)

Without schema and index details, not possible to give best solution. And the numbers that you wrote above are just execution statistics, you need to include execution plan which is in .sqlplan format for others to analyze why the query is performing slow.

sujitkmishra Mr or Mrs. 500 Points: 524 More actions · Answer 6

Please post the ACTUAL EXECUTION PLANS of the queries. In addition, please send the output of the following have a better picture of the server.

sp_configure (With show advanced options on)

xp_msver

Also please mention if you have changed and database level or server level options on the server with SQL2K8? I hope the compatibility level of the databases after migration is that of SQL2K8.

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 7

have you checked if there are any blocing processes on any of these objects.

Jayanth Kurup[/url]

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 8

sorry Mr.

schema name - DBO, and attached file for execution plan file, please find out.

I have changed set ompatible level 100 after upgraded

thanks

sujitkmishra Mr or Mrs. 500 Points: 524 More actions · Answer 9

You can see in the plan that it's clearly stating that statistics are missing on columns.

First thing first please do a update stats with full scan on the whole database (I 'wd strongly suggest) and then try rerunning the queries again. Do post the actual execution plan if the problem persists.

Ken McKelvey SSCoach Points: 18838 More actions · Answer 10

Looking at your views, I suspect blocking as well.

(Bad stats may not help with this.)

Try just using the tables and doing a FULL JOIN.

Something like:

SELECT

CASE WHEN BIN.[Site] IS NULL THEN 'Bincard' ELSE 'List Station' END AS Anomaly_In

,COUNT(1) AS Total

FROM dbo.TEL_TXT_LI_ST ST

FULL JOIN dbo.Tel_Bincard_Detail BIN

ON ST.[Site] = BIN.[Site]

AND ST.A = BIN.Ext

AND BIN.Remove_Date IS NULL

WHERE ST.[Site] IS NULL

OR BIN.[Site] IS NULL

GROUP BY CASE WHEN BIN.[Site] IS NULL THEN 'Bincard' ELSE 'List Station' END