March 23, 2009 at 4:24 am
I have a View that needs to be a good deal faster than it is, but I'm getting nowhere beyond shaving a few seconds off it.
It's a bit of a beast, with 9 Inner Joins and 28 Left Joins, 15 of which are Left Joins onto another view (each joining the same view against a different value within a specific column).
If I wrote this as a SP I can get it down to a few seconds, because I can use temporary tables, specify indexes and such exciting things - but it has to be a view.
As a view it takes several minutes and times out. Of course we can change the time-out, but I think this View should be better than it is.
I would really like to use a SP instead, but this isn't an option. So, is there any way I can call a SP or suchlike from the View, which could set up a table that the view could then use?
I don't think there is, but if there is a way then there are quite a few Views of similair structure here that would benefit.
March 23, 2009 at 5:56 am
Can you post the query, the table definitions and any indexes on them?
A view has to be a single select statement, so no EXEC in it . Why does it have to be a view?
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
March 23, 2009 at 6:17 am
I agree with Gail's comments (we need more information to help you, why it would be a view?, ...), but for contribute with something more,
Have you checked the execution plan?
(Table Scans, Index Scans, A lot of bookmark lookups, ...)
March 23, 2009 at 8:11 am
Thanks for the reply to you both.
It needs to be a view because deep within the bowels of a VB system it is geared towards opening a table, and specify filters within the VB. The VB thinks it is just opening a table and passing filters through, so a SP would mean changes to the VB project also, within a Class that is used throughout several systems.
The real problem within the execution seems to be the other view that it has a Left Join to in 15 different places, creating Derived Tables. When I rewrote it as a SP, just updating a temp table with that view and using the temp table instead made a dramatic difference; anything else just shaved small amounts from the execution time.
I tried a CTE instead of the additional view, but that only saved a small amount of processing time, nothing like the savings for a temporary table.
Because of the amount of company information that exists wthin the tables and suchlike I would have to edit the tables and view before posting them. Not a problem for the view (although not a short job in itself) but 22 tables that it access would be more entertaining.
March 23, 2009 at 8:19 am
To be blunt, your best bet is to begin to re-engineer the whole system so that it doesn't use such a view. From what you're describing so far, it sounds like some dev who didn't understand scalability built the thing for his own convenience. I've seen a lot of "view to a kill" solutions (meaning "I'm going to use a view, even if it kills performance, because it makes it really easy for me to write my queries").
Avoiding a rebuild, have you looked into indexed views? They might allow you to get better performance on your selects, at least.
Edit: Just noticed what forum this one is in. You mentioned CTEs. Is the database actually in SQL 2005, and the question is just in the wrong forum?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 23, 2009 at 8:28 am
in the end, you vb is still just using an ADODB.Recordset and applying filters, right?
vb can take a stored proc's results, or a vb6 function that returns an ADODOB.Recordset, and you can use your existing stored procedure instead of re-writnting your view.
SET myRecordset = myConnection.Execute("EXEC myProcedure")
or with a CommandObject if you have parameters:
Dim MyCmd 'As ADODB.Command
Set MyCmd = Server.CreateObject("ADODB.Command")
MyCmd.CommandText = "myProcedure "
MyCmd.CommandType = 4 'adCmdStoredProc
Dim MyParam 'As ADODB.Parameter
Set MyParam = Server.CreateObject("ADODB.Parameter")
MyParam.Name = "@NomGarage"
MyParam.Value = TRIM(Request.Form("BookTitle"))
MyParam.Size = 50
MyParam.Direction = 1 'adParamInput
MyParam.Type = 200 'adVarChar
MyCmd.Parameters.Append MyParam
Dim MyRs 'As ADODB.Recordset
Set MyRs = Server.CreateObject("ADODB.Recordset")
MyRs.CursorLocation = 3 'adUseClient
MyCmd.ActiveConnection = MyCn 'your connection object
MyRs.Open MyCmd, , 1, 3
If Not MyRs.EOF Then
'you have results to show
Else
'err mess?
End If
Set MyParam = Nothing
Set MyCmd = Nothing
BrainDonor (3/23/2009)
Thanks for the reply to you both.It needs to be a view because deep within the bowels of a VB system it is geared towards opening a table, and specify filters within the VB. The VB thinks it is just opening a table and passing filters through, so a SP would mean changes to the VB project also, within a Class that is used throughout several systems.
The real problem within the execution seems to be the other view that it has a Left Join to in 15 different places, creating Derived Tables. When I rewrote it as a SP, just updating a temp table with that view and using the temp table instead made a dramatic difference; anything else just shaved small amounts from the execution time.
I tried a CTE instead of the additional view, but that only saved a small amount of processing time, nothing like the savings for a temporary table.
Because of the amount of company information that exists wthin the tables and suchlike I would have to edit the tables and view before posting them. Not a problem for the view (although not a short job in itself) but 22 tables that it access would be more entertaining.
Lowell
March 23, 2009 at 5:20 pm
Like derived tables and CTE's, every time you reference a view in the from clause of query causes the view to be "re-executed" (results materialized) and that's gonna make for some dog slow returns. You could try to turn it into a fully materialized view by reworking it to be an indexed view. That has some pretty specific rules and some limitations and, if you do it wrong, could drastically impact inserts and updates on the underlying tables... but it's about as close as you can come to having a temp table being referenced by a view. Read up about Indexed Views in Books Online.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 23, 2009 at 5:44 pm
Try to find and eliminate all implicit conversions in your view.
They happen when you join tables by columns having not the same data type.
You may also create "temp tables" in views by including derived tables with GROUP BY statement. But make sure the query id DT is effective and returns small recordset. Otherwise it will only make things worse.
_____________
Code for TallyGenerator
March 24, 2009 at 2:28 am
My thanks to everyone who replied. I've managed to get what I wanted - a rewrite!
It is going to be the SP that I wrote and a slight change to the VB side so it will run it instead of the view. So it should go from 2+ minutes to about 10 seconds.
I'm the new guy here and I've never seen Views of such size and complexity anywhere else. To my mind Views are very useful but need to be kept simple to use them as they were intended.
I'll have a look at Indexed Views sometime as it isn't something I've played with before, and I'm always willing to look at something new with SQL.
And yes, I've just noticed that I wrote this in the wrong forum, so my apologies. I had searched through the forums before starting and wound up in 'T-SQL'. What I hadn't noticed was it was within 'SQL Server 7,2000'. I shall pay more attention next time.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply