Why my sql query is so slow in one database?

Question

I am using sql server 2008 r2 and I have two database, which is one have 11.000 record and another is just 3000 record, when i do run this query

SELECT Right(rtrim(tbltransac.No_Faktur),6) as NoUrut,
       tbltransac.No_Faktur,
       tbltransac.No_FakturP,
       tbltransac.Kd_Plg,
       Tblcust.Nm_Plg,
       GRANDTOTAL AS Total_Faktur,
       tbltransac.Nm_Pajak,
       tbltransac.Tgl_Faktur,
       tbltransac.Tgl_FakturP,
       tbltransac.Total_Distribusi 
FROM Tblcust
  INNER JOIN ViewGrandtotal AS tbltransac ON Tblcust.Kd_Plg = tbltransac.Kd_Plg 
WHERE tbltransac.Kd_Trn = 'J'
  and year(tbltransac.tgl_faktur)=2015
  And ISNULL(tbltransac.No_OPJ,'') <> 'SHOP'  
Order by Right(rtrim(tbltransac.No_Faktur),6) Desc

It takes me 1 minute 30 sec in my server (I query it using sql management tool) that have 3000 record but it only took 3 sec to do a query in my another server which is have 11000 record, whats wring with my database?

I've already tried to backup and restore my 3000 record database and restore it in my 11000 record server, it's faster.. took 30 sec to do a query, but it's still annoying if i compare to my 11000 record server. They are in the same spec

How this happend? what i should check? i check on event viewer, resource monitor or sql management log, i couldn't find any error or blocked connection. There is no wrong routing too..

Please help... It just happen a week ago, before this it was fine, and I haven't touch the server more than a month...

_year(tbltransac.tgl_faktur)=2015_ and your Order doesnt work with index because you have function in this statement — The Reason, Jun 11 '15 at 09:29
yes, they are the same database, just different shop that using it — Ke Vin, Jun 11 '15 at 09:30
Just a tip. do Order by NoUrut DESC instead. Easier to read and understand. — jarlh, Jun 11 '15 at 09:31
What's ViewGrandtotal? Since we don't know the definition, we can't answer the question - only guess! — jarlh, Jun 11 '15 at 09:33
General tip: Check the option "Show ACTUAL Execution Plan" in SQL Studio. That way, you can find the problem more easily. — SQL Police, Jun 11 '15 at 10:57

score 1 · Answer 1 · edited Jul 29 '20 at 18:30

Note: first of all you should avoid any function in Where clause like this one

year(tbltransac.tgl_faktur)=2015

Here Aaron Bertrand how to work with date in Where clause

"In order to make best possible use of indexes, and to avoid capturing too few or too many rows, the best possible way to achieve the above query is ":

SELECT COUNT(*)
      FROM dbo.SomeLogTable
      WHERE DateColumn >= '20091011'
      AND DateColumn < '20091012';

And i cant understand your logic in this piece of code but this is bad part of your query too

ISNULL(tbltransac.No_OPJ,'') <> 'SHOP'

Actually Null <> "Shop" in this case, so Why are you replace it to ""?

Thanks and good luck

score 1 · Answer 2 · edited May 23 '17 at 11:51

Here is some recommendations:

year(tbltransac.tgl_faktur)=2015 replace this with tbltransac.tgl_faktur >= '20150101' and tbltransac.tgl_faktur < '20160101'
ISNULL(tbltransac.No_OPJ,'') <> 'SHOP' replace this with tbltransac.No_OPJ <> 'SHOP' because NULL <> 'SHOP'.
Order by Right(rtrim(tbltransac.No_Faktur),6) Desc remove this, because ordering should be done in presentation layer rather then in data layer.

Read about SARG arguments and predicates:

What makes a SQL statement sargable?

To write an appropriate SARG, you must ensure that a column that has an index on it appears in the predicate alone, not as a function parameter. SARGs must take the form of column inclusive_operator or inclusive_operator column. The column name is alone on one side of the expression, and the constant or calculated value appears on the other side. Inclusive operators include the operators =, >, <, =>, <=, BETWEEN, and LIKE. However, the LIKE operator is inclusive only if you do not use a wildcard % or _ at the beginning of the string you are comparing the column to

score 1 · Accepted Answer · answered Jun 11 '15 at 10:51

as already mentioned before, you have three issues in your query.

Just as an example, change the query to this one:

SELECT Right(rtrim(tbltransac.No_Faktur),6) as NoUrut,
       tbltransac.No_Faktur,
       tbltransac.No_FakturP,
       tbltransac.Kd_Plg,
       Tblcust.Nm_Plg,
       GRANDTOTAL AS Total_Faktur,
       tbltransac.Nm_Pajak,
       tbltransac.Tgl_Faktur,
       tbltransac.Tgl_FakturP,
       tbltransac.Total_Distribusi 
FROM Tblcust
  INNER JOIN ViewGrandtotal AS tbltransac ON Tblcust.Kd_Plg = tbltransac.Kd_Plg 
WHERE tbltransac.Kd_Trn = 'J'
  and tbltransac.tgl_faktur BETWEEN '20150101' AND '20151231'
  And tbltransac.No_OPJ <> 'SHOP'  
Order by NoUrut Desc --Only if you need a sorted output in the datalayer

Another idea, if your viewGrandTotal is quite large, could be an pre-filtering of this table before you join it. Sometimes SQL Server doesn't get a good plan which needs some lovely touch to get him in the right direction.

Maybe this one:

SELECT Right(rtrim(vgt.No_Faktur),6) as NoUrut,
        vgt.No_Faktur,
        vgt.No_FakturP,
        vgt.Kd_Plg,
        tc.Nm_Plg,
        vgt.Total_Faktur,
        vgt.Nm_Pajak,
        vgt.Tgl_Faktur,
        vgt.Tgl_FakturP,
        vgt.Total_Distribusi 
FROM (SELECT Kd_Plg, Nm_Plg FROM Tblcust GROUP BY Kd_Plg, Nm_Plg) as tc -- Pre-Filter on just the needed columns and distinctive.
INNER JOIN (
        -- Pre filter viewGrandTotal
        SELECT  DISTINCT vgt.No_Faktur, vgt.No_Faktur, vgt.No_FakturP, vgt.Kd_Plg, vgt.GRANDTOTAL AS Total_Faktur, vgt.Nm_Pajak, 
                vgt.Tgl_Faktur, vgt.Tgl_FakturP, vgt.Total_Distribusi 
        FROM ViewGrandtotal AS vgt 
        WHERE tbltransac.Kd_Trn = 'J'
        and tbltransac.tgl_faktur BETWEEN '20150101' AND '20151231'
        And tbltransac.No_OPJ <> 'SHOP'  
    ) as vgt
    ON tc.Kd_Plg = vgt.Kd_Plg 
Order by NoUrut Desc --Only if you need a sorted output in the datalayer

The pre filtering could increase the generation of a better plan.

Another issue could be just the multi-threading. Maybe your query get a parallel plan as it reaches the cost threshold because of the 11.000 rows. The other query just hits a normal plan due to his lower rows. You can take a look at the generated plans by including the actual execution plan inside your SSMS Query.

Maybe you can compare those plans to get a clue. If this doesn't help, you can post them here to get some feedback from me.

I hope this helps. Not quite easy to give you good hints without knowing table structures, table sizes, performance counters, etc. :-)

Best regards, Ionic

thank u ionic, i am so desperate to find what happened, when i try your query, my query time decrease into 20 sec, ghosss.. thank u again ionic — Ke Vin, Jun 15 '15 at 02:59
Nice to here that the solution worked for you. :-) Maybe it could be tweaked a bit, but this just can be handled with the real data in the background. Maybe some index could be beneficial for you. — Ionic, Jun 15 '15 at 11:26

Why my sql query is so slow in one database?

3 Answers3