Performance Issue -13 Minitues

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

Transact-SQL (2000)

Performance Issue -13 Minitues

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 03:19:27

Dear All,

I have table with 14708277 Rows
I need to fetch around 100000 employee from this table
The index is good, The index filed is appered in where condition
but it tooks 13 minitues to display the records

How would i improve the performance..!

The query

SELECT Pay.h01_emp_num,@Host_id

FROM P21_Pay_History_Detail Pay (nolock)

WHERE Pay.P03_Process_Period = 32
AND Pay.P09_element_code = 'ESI'
AND Pay.p21_actual_rate_basecur > 0.0
AND Pay.C03_Organisation_code = @Company_code
aND pAY.C29_LOCATION_CODE = @location_Code

The index for the table is
Clustered in 03_Process_Period,C03_Organisation_code,C29_LOCATION_CODE

Thanks
Krishnakuamr.C

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 03:32:59

How long does

SELECT [red]COUNT(DISINCT Pay.h01_emp_num)[red]

FROM P21_Pay_History_Detail Pay (nolock)

WHERE Pay.P03_Process_Period = 32
AND Pay.P09_element_code = 'ESI'
AND Pay.p21_actual_rate_basecur > 0.0
AND Pay.C03_Organisation_code = @Company_code
aND pAY.C29_LOCATION_CODE = @location_Code

take? If that's relatively quick then the rest is probably transmission time (100,000 rows is quite a lot to push to the client, although you are only selecting one column - but the client has to handle the data too of course!).

I'm guessing that there are lots of records for each P03_Process_Period, C03_Organisation_code, C29_LOCATION_CODE combination, and therefore the index selectivity is not very good, a nd the index is probably not being used.

What does

SET SHOWPLAN_TEXT ON
(your original query here)
SET SHOWPLAN_TEXT OFF

show?

Kristen

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 03:47:02

The SELECT [red]COUNT(DISINCT Pay.h01_emp_num)[red] is tooks 1.5 minitues.
We have lot of combinations for the company code and location . the company code G4FLKGRD has 59 locations.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 03:50:35

i just want to get all of the employees from the 59 location for the process period from 32 to 43.For one process period and one location it tooks 13 minitues, I dont know how can i get the records for the 12 process periods for the corresponding (59)locations..!!
krishnakumar.C

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 05:17:05

If the COUNT thingie took 1.5 minutes and the SELECT takes 13 minutes I reckon the difference is the transmission time getting the records from the server to the application (the time for the application to handle / process them is probably in that time too)

"We have lot of combinations for the company code and location "

Still worth checking the Query Plan to make sure its using your index, and not a Table Scan or somesuch.

Can you aggregate the data at the SQL end - so that you send less data to the client?

Kristen

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 06:19:14

there is no such filter condition to send a less data.
Is there possible to do by cube..!

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 06:32:10

It would be worth checking that it really is a transmission issue. You could run the query DIRECTLY ON THE SERVER and to a file (so there is no screen-redraw time).

e.g. start Query Analyser on the server (e.g. via Terminal Servers), use Control-Shift-F to specify that the output is to go to a file, run the query, enter a suitable filename, start your timer!

Did you check the Query Plan yet?

Kristen

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 07:02:07

Thanks kristen. The timing has taken from query analyser only. My thought is there is no such a tranmisson time because just i am executing the procedure in query analyser only.I have checked the query plan. I found the index scan for the payroll table. I have just added the company code and location code in non clusterd index. the process is going on. i will let u know once it'will completed.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 07:07:56

Dbcc Showcontig (P21_Pay_History_Detail)

DBCC SHOWCONTIG scanning 'P21_Pay_History_Detail' table...
Table: 'P21_Pay_History_Detail' (640721335); index ID: 0, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 350701
- Extents Scanned..............................: 43869
- Extent Switches..............................: 43868
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.93% [43838:43869]
- Extent Scan Fragmentation ...................: 52.58%
- Avg. Bytes Free per Page.....................: 336.1
- Avg. Page Density (full).....................: 95.85%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 07:21:16

"The timing has taken from query analyser only."

If you are on a workstation that includes transmission time.

Unless you are saving the data to a file it also includes the time for Q.A. to "display" the data - and 100,000 rows is a lot of data to ask Windows to create a Grid for.

1) Save it to a file and time it on a workstation.
2) If its slow repeat on the server and see if it is significantly faster - if so its transmission time.

"I found the index scan for the payroll table"

Do you mean that, and was it the index you wanted it to use, or just the PK index?

An index seek is what you need for performance. If you are getting an index-scan on the PK then that's just a whole table scan. (actually its not necessarily the PK, it would be whichever index is Clustered)

Kristen

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 07:33:42

the composite pk for the table is company_code,location_code and Process periods, that's in clusterd and one more index i have put company_code,location_code as non clustered. But those fields are in where clause.

1) Save it to a file and time it on a workstation.

This process has compeleted on 1 minitue in server. but the work station still going on for the same query..!!. Myself only using the server and workstation (Test server). nobody can access the server and workstation. there is no network traffic also. What is happening here.

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 07:37:50

this is the output in work station in 12 minitues 43 seconds

(56187 row(s) affected)

The following file has been saved successfully:

C:\Documents and Settings\krishnakc\My Documents\check.rpt 449553 bytes

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 07:39:19

this is the output in testserver in 1 minitue 49 seconds
(56187 row(s) affected)

The following file has been saved successfully:

C:\Documents and Settings\SQLTESTSEVER\My Documents\CSKcheck.rpt 449553 bytes

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-06 : 07:42:39

So finaly what is the solution Kristen...?

Kristen
Test

22859 Posts

Posted - 2006-10-06 : 08:11:10

56,187 rows is not a very big transfer, so something wrong with your network or the ADO settings for the transfer from database to client.

"the composite pk for the table is company_code,location_code and Process periods"

But those three columns aren't unique are they?

WHERE Pay.P03_Process_Period = 32
...
AND Pay.C03_Organisation_code = @Company_code
aND pAY.C29_LOCATION_CODE = @location_Code

is giving you 56,187 rows, after all

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-10-06 : 09:13:25

We could help much better if you provide us with CREATE TABLE statments, the actual indexes and keys, some sample data, and exactly what the logic is you need to apply in the SELECT. it's all guessing on our end until we know the specifics.

- Jeff

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-09 : 02:30:59

quote:
Originally posted by jsmith8858

We could help much better if you provide us with CREATE TABLE statments, the actual indexes and keys, some sample data, and exactly what the logic is you need to apply in the SELECT. it's all guessing on our end until we know the specifics.

- Jeff

Dear Jeff:

The create table syntax...
CREATE TABLE [P21_Pay_History_Detail] (
[C03_Organisation_code] [GLCOMPANY] NOT NULL ,
[C29_Location_Code] [GLLOCN] NOT NULL ,
[H01_emp_num] [empnum] NOT NULL ,
[H10_assignment_no] [tinyint] NOT NULL ,
[P06_Payroll_code] [PAYROLL] NOT NULL ,
[P03_Process_Period] [smallint] NOT NULL ,
[P09_Element_code] [ELMCODE] NOT NULL ,
[P01_Period_Unit_Code] [PRDUNIT] NULL ,
[P21_no_of_attend_units] [cmn_Plow] NULL ,
[P21_Actual_attend_units] [cmn_Plow] NULL ,
[P21_STD_Rate] [cmn_Pamt] NULL ,
[P21_Actual_Rate] [cmn_Pamt] NULL ,
[FS_currency] [GLCURRCODE] NOT NULL ,
[P21_STD_Rate_Basecur] [cmn_Pamt] NULL ,
[P21_Actual_Rate_Basecur] [cmn_Pamt] NULL ,
[P21_Pay_Run_Tag] [char] (1) COLLATE SQL_1xCompat_CP850_CI_AS NULL ,
[P21_Conversion_Rate] [cmn_Pexch_rate] NULL ,
[P21_Dummy1] [QCD] NULL ,
[P21_Dummy2] [QCD] NULL ,
[User_Id] [smallint] NOT NULL ,
[Modified_Date] [datetime] NOT NULL ,
[Time_Stamp] [timestamp] NULL
) ON [PRIMARY]
GO

--Indexes
CREATE INDEX [P21_Idx] ON [dbo].[P21_Pay_History_Detail]([C03_Organisation_code], [C29_Location_Code], [P03_Process_Period]) WITH FILLFACTOR = 70 ON [PRIMARY]
GO

thanks
krishnakumar.C

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-10-09 : 02:38:11

quote:
Originally posted by Kristen

56,187 rows is not a very big transfer, so something wrong with your network or the ADO settings for the transfer from database to client.

"the composite pk for the table is company_code,location_code and Process periods"

But those three columns aren't unique are they?

WHERE Pay.P03_Process_Period = 32
...

AND Pay.C03_Organisation_code = @Company_code
aND pAY.C29_LOCATION_CODE = @location_Code

is giving you 56,187 rows, after all

Kristen

Sorry Kristen.. There is no Pk, I have wrongly typed.
it's in composite clustered index only. Now i have changed as a nonclusterd index as per my senior advice..

Thanks
krishnakumar.C

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 03:38:42

If you are receiveing the records on 90 seconds and the do a MOVENEXT for each of all records, 13 minutes is not that bad.
What you should do CLIENT side is to call .GetRows function to get all records at once.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources