All Forums Database
super25 19 posts Joined 07/11
19 Jan 2012
Performance problem

I have a table of 2tb with MV compression and when i do a query using a where clause of an indiacator which has just 2 values either 0 or 1, the performance is really bad there is a difference of almost 30min when compared to qry not using the where cluase with indicator field.

The table Employee.empdetails is partioned on empid,emp_state and emp_ind.

select top 10* from Employee.empdetails where emp_ind=1; --- 30min to run

select top 10* from Employee.empdetails ;  -- leass than a sec to run

How can i imnprove this?

 

ulrich 816 posts Joined 09/09
19 Jan 2012

Hi,

don't compare apples with pears.

Top 10 will give you the first 10 rows of the final result set. 

Select top 10 * from Employee.empdetails 

has no contrains therefore 10 rows will be found imidiatly.

select top 10* from Employee.empdetails where emp_ind=1; 

Needs first to apply the filter and when this is done the first 10 rows are returned.

Check the two explains.

You could also consider to change the order of your PPI if empid,emp_state and emp_ind is the order of your different levels.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

super25 19 posts Joined 07/11
19 Jan 2012

yes, i tried that already it does not make any difference.

ulrich 816 posts Joined 09/09
19 Jan 2012

what is the demographic (row counts) of the two values 0 and 1?

And what do you want to achive?

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

super25 19 posts Joined 07/11
19 Jan 2012

80% of the records has 1 and the rest 0 but most the time we qry on 1.

ulrich 816 posts Joined 09/09
19 Jan 2012

so what do you expect if you query a 2TB table with an condition which returns 80% - 1.6TB - of the rows?

As mentioned 

select top 10* from Employee.empdetails where emp_ind=1;

will first create a 1.6TB spool file before top10 * is going to be evaluated

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

super25 19 posts Joined 07/11
19 Jan 2012

that makes sense i agree but in this situation what are the options i have to gain performance.

ulrich 816 posts Joined 09/09
20 Jan 2012

gain performance for what? Read 1.6 TB faster? buy more HW.

Getting faster responce for 

 

select top 10* from Employee.empdetails where emp_ind=1;?

Create a single table join index with the where condition - but this needs space and has other costs. And what is the business value to speed up this query?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.