Querying from SQL takes a longer time...Performance Issue

Question

Suppose I have a Select Statement like this...

select a,b,c 
from table1 where d='' 
union 
select a,b,c 
from table1 where d>='' and e='' order by f asc,g desc offset 0 rows 
fetch next 100 rows only

Lets say I need both the select with the union but the thing is when I do order by with non-primary keys i.e. with f and g it takes a lot of time to execute the query. But when I do the order by with primary keys say a and b it executes faster.

Please suggest me a way where I can execute the above query faster with order by of non-primary keys.

Can you specify what is your DB engine? If you SQL Server Enterprise edition, you can use indexed views. — Steve B, Feb 17 '14 at 13:24
Maybe you need to create some [indexes](http://stackoverflow.com/questions/2955459/what-is-an-index-in-sql-server) — Alberto, Feb 17 '14 at 13:28
@GordonLinoff I mean to say d='some value' but the field f has values null — user3085636, Feb 17 '14 at 13:30
Again, please specify what is your DB engine (use the appropriate tag and/or edit your question). That said, startpage.com will tell you that this topic is [described somewhere in technet](http://technet.microsoft.com/en-us/library/ms187864.aspx) — Steve B, Feb 17 '14 at 13:34
@user3085636 ***what*** is "Non Clustered index"? What column or columns does each index span? — Marc Gravell, Feb 17 '14 at 13:35

score 0 · Answer 1 · edited May 23 '17 at 12:03

Personally I'd start by removing the UNION leaving you something like:

SELECT
    a,b,c 
FROM
    table1 
WHERE 
    ISNULL(d,'') = ''
    OR ((NOT ISNULL(d,'') = '') AND e='')
ORDER BY 
    f ASC
    , g DESC
OFFSET 0 rows FETCH NEXT 100 ROWS ONLY

I'd then review the indexes assigned to the database and probably add f and g as indexed, if you're not confident with indexing, I'd suggest either doing a (insert search engine name here) to find suitable articles or even review other Stack Overflow questions such as this one. If you have the facility you should consider using an SQL Profiler to determine where indexes would benefit your database and improve its performance.

Another thing worth looking at is the efficiencies of your paging method, if you're only ever wanting the first 100 results you could do something like SELECT TOP 100... or you could look at this article which details efficient paging. I've not used OFFSET n ROWS SELECT NEXT 100 ROWS ONLY myself but have used ROW_NUMBER()OVER() (see here) and not had too many problems myself.

Querying from SQL takes a longer time...Performance Issue

1 Answers1