View Vs T-SQL in QA

Question

View Vs T-SQL in QA

Mike Levan

SSChampion

Points: 14201
More actions
December 16, 2008 at 11:03 am

#197599

I see lot of difference performance wise when running the same query in QA and same from view. Quering running from Query Analyser takes 15min but when a view with the same query in it takes more than 3hrs, thats a vast diff. How coulr i make my view run faster.
here is the query and in this state4.state_PA.dbo.revenue is the view
select a.OfficeID, provzip as zipcode
into #PAzip2
from (
select a.OfficeID, min(servcdate) as firstdate
from #PAfindrest as a inner join state4.state_PA.dbo.revenue as b
on a.OfficeID=b.OfficeID
where b.servcdate<=a.firstfill
group by a.OfficeID
) as a inner join state4.state_PA.dbo.revenue as b
on a.OfficeID=b.OfficeID
where a.firstdate=b.servcdate

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First of all, the code above isn't a view, it is a SELECT INTO statement creating a temporary table joined on another temporary table.

Is it possible to get all the code involved in this issue instead of just a piece of it?

Also, you posted this in a SQL Server 2005 forum but mention QA, are you using SQL Server 2005 or SQL Server 2000?

Mike Levan SSChampion Points: 14201 More actions · Answer 2

Here is the original view where above query is looking into

ALTER VIEW [dbo].[Revenue]
AS
SELECT * FROM Reval204
where RevCode = 'I'
and (Rev_ID is null or Rev_ID='PET')
UNION ALL
SELECT * FROM Reval205
where RevCode = 'I'
and (Rev_ID is null or Rev_ID='PET')
UNION ALL
SELECT * FROM Reval206
where RevCode = 'I'
and (Rev_ID is null or Rev_ID='PET')
UNION ALL
SELECT * FROM Reval207
where RevCode = 'I'
and (Rev_ID is null or Rev_ID='PET')
UNION ALL
SELECT * FROM Reval208
where RevCode = 'I'
and (Rev_ID is null or Rev_ID='PET')

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

And if you want advice for performance, as you well know by now, we need the table definitions, the index definitions and, if on 2005 or higher, the execution plan saved as a .sqlplan file.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mike Levan SSChampion Points: 14201 More actions · Answer 4

The weired thing is same process runs gud on a different database.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Mike Levan (12/17/2008)
The weired thing is same process runs gud on a different database.

"runs gud" is a relative term.

With a join between a local table and a linked server table, and the same linked server table joined again to the product, you're asking for performance trouble.

Which table contains the column provzip?

Something along the lines of this...SELECT a.OfficeID, provzip AS zipcode

INTO #PAzip2

FROM (SELECT OfficeID, MIN(servcdate) AS firstdate

FROM state4.state_PA.dbo.revenue

GROUP BY a.OfficeID) b

INNER JOIN #PAfindrest c ON c.OfficeID = b.OfficeID AND b.firstdate <= c.firstfill

...is likely to give you better performance.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

EdVassie SSC Guru Points: 60481 More actions · Answer 6

Some time ago on SQL Server 2000 SP4, I noticed a problem with view that contains a UNION:

a) You have a view with a number of UNION ALL statements

b) You run some SQL that SELECTs from the view, applies a predicate to a column in the view, and joins the view to other tables.

The resulting access plan showed that SQL Server did not apply the predicate when retrieving the data from the view. Instead it materialised the view in a workfile than applied the predicate when selecting data from the workfile.

If the process was repeated but with the statements forming the view expressed in-line with the query, the predicate was applied when selecting the data.

Even though in theory the two queries produced identical SQL after the view text had been merged by the optimiser, because SQL chode to materialise the view then the query using the view took an order of magnitude longer then expressing everything in a query.

The choice of access path did seem to be influenced by data volume. On an almost emprt Dev system the predicate was drilled down into the view, but on Production with multi-million row tables SQL preferred to materialise the view.

I no longer have access to that system, so I don't know if the same behaviour would apply with current maintenance levels or more recent versions of SQL Server. This does not solve the OP problem, but maybe gives an insight to a possible cause.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara