overheat performance query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

overheat performance query

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2012-01-05 : 18:44:06

Good evening i've got this query but according to execution plan (table scan) it takes to much time to run this query (nearly 2 hours)even thouh is just for 1 day.

DECLARE @FECHA_INI CHAR(8)
DECLARE @FECHAFIN CHAR(8)
SET @FECHA_INI = '20120103'
SET @FECHAFIN = '20120304'
WHILE @FECHA_INI < @FECHAFIN
BEGIN
SELECT @FECHA_INI,COUNT(1) CANTIDAD
FROM ACA_INCIDENCIA
WHERE ((FEC_CREACION >= @FECHA_INI AND FEC_CREACION < @FECHAFIN)
OR (FEC_REAL >= @FECHA_INI AND FEC_REAL < @FECHAFIN)
OR (FEC_HISTORICO >= @FECHA_INI AND FEC_HISTORICO < @FECHAFIN))
AND FUENTE = 1
SET @FECHA_INI = CONVERT(VARCHAR,DATEADD(DD,1,@FECHA_INI),112)
END

And i modify the query cuting off the 2 lines containing the operator AND and the execution plan turn and index seek and the took 10 minutes OK (great) but the resulset is different fom the first one
The index for the table are:IX_ACA_INCIDENCIA|nonclustered located on INDICES|TIP_INCIDENCIA
IX_ACA_INCIDENCIA_1|nonclustered located on PRIMARY|FEC_CREACION
IX_ACA_INCIDENCIA_FUENTE|nonclustered located on INDICES|FUENTE
PK_ACA_INCIDENCIA|nonclustered, unique, primary key located on INDICES|NUM_INCIDENCIA, FUENTE
An the data types fot the fields are:
FEC_CREACION|datetime|no|8|
FEC_REAL|datetime|no|8| |
| |yes|(n/a)|(n/a)|

Id appreciate your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-05 : 19:06:07

I am not clear what your query is doing, but it sounds like you are missing an index. What indexes do you have on that table?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

alejo46
Posting Yak Master

157 Posts

Posted - 2012-01-05 : 20:20:43

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-01-05 : 22:12:22

Why are you looping through this data? Can't you return it all in one result set? Instead of the ORs in there, I'd recommend using a UNION ALL query which should avoid the scan.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

alejo46
Posting Yak Master

157 Posts

Posted - 2012-01-10 : 01:32:57

Good evening, i modify the query adding UNION ALL into the query and run the query, but the degradation persist and in the otherhand it takes much more time than the former query:

SET NOCOUNT ON
DECLARE @FECHA_INI CHAR(8)
DECLARE @FECHAFIN CHAR(8)
SET @FECHA_INI = '20120103'
SET @FECHAFIN = '20120104'

WHILE @FECHA_INI < @FECHAFIN
BEGIN

SELECT @FECHA_INI,COUNT(1) CANTIDAD
FROM ACA_INCIDENCIA
WHERE (FEC_CREACION >= @FECHA_INI AND FEC_CREACION < @FECHAFIN)
AND FUENTE = 1
UNION ALL

SELECT @FECHA_INI,COUNT(1) CANTIDAD
FROM ACA_INCIDENCIA AI
WHERE (FEC_REAL >= @FECHA_INI AND FEC_REAL < @FECHAFIN)
AND FUENTE = 1

UNION ALL

SELECT @FECHA_INI,COUNT(1) CANTIDAD
FROM ACA_INCIDENCIA AI2
WHERE (FEC_HISTORICO >= @FECHA_INI AND FEC_HISTORICO < @FECHAFIN)
AND FUENTE = 1

END

Its necessary to konw that in this query the only filed that has index is FEC_CREACION:

index_name|index_description|index_keys
IX_ACA_INCIDENCIA|nonclustered located on INDICES|TIP_INCIDENCIA
IX_ACA_INCIDENCIA_1|nonclustered located on PRIMARY|FEC_CREACION
IX_ACA_INCIDENCIA_FUENTE|nonclustered located on INDICES|FUENTE
PK_ACA_INCIDENCIA|nonclustered, unique, primary key located on INDICES|NUM_INCIDENCIA, FUENTE

What should i do,Thanks in advance for your help

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-10 : 04:22:24

But.... WHY ARE YOU LOOPING THROUGH THE DATA

Tara -- I don't think you said that loud enough!

You are performing 60 odd SELECT statements. (one for each day). That means you get multiple separate result sets. Generally that's not what you want.

Surely if you just did 1 SELECT statement and then split the results in the calling application. (ORDER BY is your friend here)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

alejo46
Posting Yak Master

157 Posts

Posted - 2012-01-10 : 09:46:39

Thanks a lot Charlie, what you meant its not necessary LOOPING THROUGH THE DATA ? -

2nd, how did you calculate "60 odd SELECT statements. (one for each day). That means you get multiple separate result sets"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-10 : 09:55:26

SET @FECHA_INI = '20120103'
SET @FECHAFIN = '20120304'

shows us: not for one day -> from january to march!

No, you're never too old to Yak'n'Roll if you're too young to die.

alejo46
Posting Yak Master

157 Posts

Posted - 2012-01-10 : 10:08:11

THanks again charlie, you gave me a shed light to develop the query much better

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-10 : 10:10:04