SQl code tuneup

Question

SQl code tuneup

Tara-1044200

SSCoach

Points: 15951
More actions
July 28, 2009 at 11:35 am

#203250

Is there any other alternatives for this code to perform better and faster. I know there are many wasy to get things done in sql code but this is what i use and it is very slow.
select distinct a.EMPID, sdate
into #temp1
from dbo.vwJobcd as a inner join #tabdates as b
on a.EMPID = b.EMPID, #MainTemp
where (sdate between startdate and enddate)
and
(sdate between effectivedate and expirationdate)
and
(empcode like 'rvg%' or empcode like 'jhy%' or
empcode in ('sdfsg','jfgyd','csdfgg','csdgg'))

Viewing 6 posts - 1 through 5 (of 5 total)

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

Tara-1044200 SSCoach Points: 15951 More actions · Answer 1

just curious to know.. if am using temp tables in the query does it reside in RAM and so the query may be slow when comapred to physical tables.

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

You need to add a table alias to #MainTemp and all columns cited in the WHERE clause so we can tell if you've made an accidental cross-join or not. Also, it's really hard to tell what's going on because we don't know how many rows will qualify for each table according to any given date range.

Last but not least, do any of these table have any indexes?

My recommendation would be to familiarize yourself with the article in the second link in my signature below and repost your question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tara-1044200 SSCoach Points: 15951 More actions · Answer 3

total result comes to 159752 records , wired thing is i can run the same query in a second but another user who is a developer its taking for 3 hrs, what might be the reason and where i need check. I didnt found any locks in the activity monitor when he was runing.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

select distinct

a.EMPID,

sdate

into

#temp1

from

dbo.vwJobcd as a

inner join #tabdates as b

on a.EMPID = b.EMPID

, #MainTemp

where

(sdate between startdate and enddate)

and (sdate between effectivedate and expirationdate)

and (empcode like 'rvg%' or empcode like 'jhy%' or

empcode in ('sdfsg','jfgyd','csdfgg','csdgg'))

Looking at the code above, it really looks like a cross join is involved. I don't see any criteria in the where clause to join the tables unless it is the two statements using the between operators.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

We can't help you any further because we don't have enought information.

Please prefix all your column names with table name or alias.

select distincta.EMPID,

sdate

into#temp1

fromdbo.vwJobcd as a

inner join#tabdates as b on a.EMPID = b.EMPID

CROSS JOIN#MainTemp

where(sdate between startdate and enddate)

and (sdate between effectivedate and expirationdate)

and (

SUBSTRING(empcode, 1, 3) IN ('rvg', 'jhy')

or empcode in ('sdfsg','jfgyd','csdfgg','csdgg')

)

N 56°04'39.16"
E 12°55'05.25"