Performance problem

Database
Enthusiast

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?

7 REPLIES
Senior Supporter

Re: Performance problem

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.

Enthusiast

Re: Performance problem

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

Senior Supporter

Re: Performance problem

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

And what do you want to achive?

Enthusiast

Re: Performance problem

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

Senior Supporter

Re: Performance problem

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

Enthusiast

Re: Performance problem

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

Senior Supporter

Re: Performance problem

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?