[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

Querry slow

I have a view that returns 1300 records and when executed with variables takes 3 seconds. if i run it with static parameter takes 0 seconds. Why is this happening?

The querries are:
1. SELECT * FROM FACC_V_PRELUARE_CONT_BUFFER WHERE DAT_DOCUMENT between '08/01/2007' and '08/31/2007'
--this take 0 seconds
2.

declare @currentdate datetime
declare @fistday datetime
declare @lastday datetime
set @currentdate=cast('8/26/2007' as datetime)

SET @fistday = DATEADD(DD, 1 - DAY(@currentdate), @currentdate)
SET @lastday = DATEADD(DD, -1, DATEADD(M, 1, @fistday))

SELECT * FROM FACC_V_PRELUARE_CONT_BUFFER
            WHERE DAT_DOCUMENT between @fistday  and @lastday
--takes 3 seconds

Both querries returns the exact number of records.
How can i modify the querry to get all the records in a month and takes 0 seconds?


this also takes 3 seconds to complete:
SELECT * FROM FACC_V_INVALIDARE_DOCS WHERE YEAR(DAT_DOCUMENT)=YEAR(@CURRENT_DATE) AND MONTH(DAT_DOCUMENT)=MONTH(@CURRENT_DATE) ORDER BY DAT_DOCUMENT
0
so3
Asked:
so3
  • 3
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what is the data type of the column DAT_DOCUMENT?
are the statistics up-to-date on that table?
what indexes do you have on that table?
is FACC_V_PRELUARE_CONT_BUFFER actually a view?

can you provide some information on how the execution plan differs for the 2 first queries?

note that the last suggestion is the worst you could have :) while the second looks absolutely fine...
0
 
so3Author Commented:
- column DAT_DOCUMENT is datetime of corse
- FACC_V_PRELUARE_CONT_BUFFER yes it is a view
- i have index on the main table that has column dat_document but the view FACC_V_PRELUARE_CONT_BUFFER use the column DAT_DOCUMENT from another view(which is actualy a union from several tables)

- are the statistics up-to-date on that table? -- sorry i don't know what do yo refer

Do you have any idea why the time on the 2 querries are different since the query results are the same?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
UNION ... a UNION or UNION ALL... should be UNION ALL


in regards to the statistics, run this query:

DBCC SHOW_STATISTICS
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
cmhuntyCommented:
How long does query 2 take without the select at the end? It should be as good as instant.
0
 
so3Author Commented:
angelIII,
- When using DBCC SHOW_STATISTICS i get an error not enough parameter
- I've put union all in that view instead of union and now the querry 2 takes 0 seconds, but if i order it by DAT_document it increases at 3 sec.
Can you explain the diference between union and union all because it seems that have impact on performance(at least on querry 2)?

cmhunty,
if i remove the where condition it takes 0 seconds. It seems that when using in the querry column dat_document takes it long.

If the main table has an index on column dat_document, why the other querries(views) don't use that index? i'm doing wrong someting or what?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
union vs union all:
union all does, implicitely, perform a DISTINCT on the resultset, which most beginners don't know. this will of course have results on the performance, but also potentially on the returned records. most of the time, though, union all is perfectly fine, and union could even "spoil" the wanted results...

now, when you add an order by, it will, of course, have to sort the results.
you might consider using a temp table (or better a table variable) to put the result unsorted in there, and then query from that table using the order by.
that will avoid that the execution plan get's impacted on the original view by using the order by.

that will also help you to avoid the SELECT * syntax, as that is not recommended, as simply, you don't need all columns.
0
 
so3Author Commented:
thanks for the help
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now