Need to Rewrite a query for performance reasons

Question

Need to Rewrite a query for performance reasons

Tarun Upadhyaya-195062

SSC Journeyman

Points: 97
More actions
February 19, 2005 at 12:11 am

#163695

Hi,
Consider the following table struture (It is actually big but i have simplified it for posting) Please note that table structure can not be changed, since this a historical data and i need to use it for a report. As soon as an employee gets transfered from one dept to another the old dept and transfer date is insert with a new row in the following table.
CREATE TABLE [transfer] (
[empid] [int] NULL , ---Employee ID
[deptcode] [char] (4) NULL , ---Old Department Code (Last worked)
[trans_date] [datetime] NULL ,----Date of transfer
[trans_code] [int] IDENTITY (1, 1) NOT NULL , ---Autogenerated ID
CONSTRAINT [pk_Transfer_trans_code] PRIMARY KEY CLUSTERED
(
  [trans_code]
) ON [PRIMARY]
) ON [PRIMARY]
GO
Sample Data
EmpID     DeptCode        Trans_Date      TransCode
101           sale                2004-01-01      5
101           prod               2004-04-01      6
101           fin                  2004-07-01      7
101           mgmt              2004-10-01      8

for ur help i have given the insert statements
-----------------------------------------------------------
INSERT INTO [transfer]([empid], [deptcode], [trans_date])
VALUES(101, 'sales', '1 jan,2004')
INSERT INTO [transfer]([empid], [deptcode], [trans_date])
VALUES(101, 'prod', '1 april,2004')
INSERT INTO [transfer]([empid], [deptcode], [trans_date])
VALUES(101, 'fin', '1 july,2004')
INSERT INTO [transfer]([empid], [deptcode], [trans_date])
VALUES(101, 'mgmt', '1 oct,2004')
-----------------------------------------------------------
The above data can be read as
The employee first transfered to Sale dept then from sale to prod, then from prod to fin and so on
I like to retrive data in the following fashion
empid       [Transfered From] [Transfered To] Date
101         sale            prod          2004-04-01 00:00:00.000
101         prod            fin           2004-07-01 00:00:00.000
101         fin             mgmt          2004-10-01 00:00:00.000
101         mgmt            NULL          NULL
The query which i have written to get the above format of data is as follows
SELECT t1.empid,t1.deptcode AS [Transfered FROM],
(SELECT deptcode FROM transfer WHERE trans_code=(SELECT min(trans_code) FROM transfer WHERE trans_code>t1.trans_code and empid=101))AS [Transfered To],
(SELECT trans_date FROM transfer WHERE trans_code=(SELECT min(trans_code) FROM transfer WHERE trans_code>t1.trans_code and empid=101))AS [Transfer Date]
FROM transfer AS t1
WHERE t1.empid=101
The query works fine but has the following disadvantages
1. It has low performance
    The statistics results are (just for 4 rows)
    Table 'transfer'. Scan count 15, logical reads 30, physical reads 0, read-
    head reads 0.
2. If i need to have more columns i need to write more subqueries
It would be a great help if someone rewrites the above query using joins or some other high-performance method.
Regards

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

You could consider writing the query using an outer join (to keep your last null row) to join the table to itself (alias it as TransferFrom and TransferTo) with the join condition being transferFrom.trans_Code + 1 = transferTo.trans_Code.

What indices do you have on the table? You may well find that an index on the trans_Code and empID columns could help a great deal.

Finally, the join suggestion above assumes that the transcodes are sequential for each employee, which in real life would most likely not be the case. Thus, you could replace the transferTo table with a derived table doing something like

select T.transCode, min(minTransfer.Trans_code)

from transfer T

left outer join transfer minTransfer

on T.empid = minTransfer.empID

and T.trans_code < minTransfer.trans_code

group by T.transCode

Sorry for the fragmented suggestions - am thinking as I type and am recovering from a night bigger than I expected

Hope this helps - if you need more suggestions, i'll check the forum again l8r

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Actually, there is an FAQ on this site that deals with something quite similar to your problem. Visit:

http://www.sqlservercentral.com/faq/viewfaqanswer.asp?categoryid=3&faqid=364

As an aside, I've been a member here for a couple of years and only noticed the FAQs for the first time ever today - perhaps there should be a section in the newsletter about them?

Cya!

Tarun Upadhyaya-195062 SSC Journeyman Points: 97 More actions · Answer 3

Hi,

Thanks for the response.

I tried creating a NC index on empid, it did helped in reducing the reads and scans but for column related thing (retrieval of N columns , as presentaly i tried for two only ), the issue is still the same. I still need to analyze the query you have suggested to me. In the mean time , I will appreciate if you can suggest something.

Regards