February 15, 2002 at 12:16 pm
This is probably a dumb question, but can I pass a variable to a view? I am way too far into a project to start experimenting.
KB
February 15, 2002 at 12:53 pm
Nope. You can either apply a where clause to the view when you use it in a select, or use a function to do about the same, or you can use a stored procedure to which you pass a parameter. You can return a rowset from a proc, not join to it (at least not easily).
Andy
February 18, 2002 at 6:32 am
Thanks. You have saved me a lot of time. I have a stored proc that selects from about 26 views (that select from other views) to review and update. It times out in VB.
KB
February 18, 2002 at 7:16 am
You can increase the command timeout in ADO, but that's usually a bandaid for bad query plans. The views in themselves aren't bad, just have to look at how you access them individually, see if you have appropriate indexes. You might also want to look at indexing the view (SQL2K only) which essentially denormalizes data to gain performance. Before you try it, I'd suggest you look at trying to come up with a "better" solution - solve the problem a different way. Post code if you'd like us to help with ideas!
Andy
February 18, 2002 at 7:19 am
Or get someone else to look the queries over the queries. Helps when more than one pair of eyes look at it. You really need to avoid the timeout change if possible.
February 18, 2002 at 10:10 am
Andy - The code for the sp is 11 pages in Word, and that does not include the views that are used. I know that one of the problems is multiple layered views. If you could help me work through one, I'm sure I could do the rest. Here is one set:
CREATE VIEW dbo.CTBAvSepDepC
AS
SELECT dbo.CTBAtblSAASCounts.LoadDate,
dbo.CTBAtblCustomer.PlanSponsorNo,
dbo.CTBAtblCustomer.ControlNo,
dbo.CTBAtblCustomer.SuffixNo,
dbo.CTBAtblCustomer.AccountNo, 'SepDepC' AS SAASField,
dbo.CTBAtblSAASCounts.SepDepC AS [Count], dbo.CTBAtblCustomer.BillOptionID,
dbo.CTBAvCurrentDraftFreq.FSADepFreqDraft,
dbo.CTBAtblCustomer.BillLevel
FROM dbo.CTBAtblCustomer INNER JOIN
dbo.CTBAtblSAASCounts ON
dbo.CTBAtblCustomer.ControlNo = dbo.CTBAtblSAASCounts.ControlNo
AND
dbo.CTBAtblCustomer.SuffixNo = dbo.CTBAtblSAASCounts.SuffixNo
AND
dbo.CTBAtblCustomer.AccountNo = dbo.CTBAtblSAASCounts.AccountNo
INNER JOIN
dbo.CTBAvCurrentDraftFreq ON
dbo.CTBAtblCustomer.ControlNo = dbo.CTBAvCurrentDraftFreq.ControlNo
AND
dbo.CTBAtblCustomer.SuffixNo = dbo.CTBAvCurrentDraftFreq.SuffixNo
AND
dbo.CTBAtblCustomer.AccountNo = dbo.CTBAvCurrentDraftFreq.AccountNo
WHERE (dbo.CTBAtblSAASCounts.SepDepC <> 0) AND (NOT (LEFT(dbo.CTBAtblCustomer.BillOptionID, 2)
= 'SP'))
CREATE VIEW dbo.CTBAvSepDepCRateID
AS
SELECT dbo.CTBAvSepDepC.LoadDate,
dbo.CTBAvSepDepC.PlanSponsorNo,
dbo.CTBAvSepDepC.ControlNo, dbo.CTBAvSepDepC.SuffixNo,
dbo.CTBAvSepDepC.AccountNo, dbo.CTBAvSepDepC.SAASField,
dbo.CTBAvSepDepC.[Count], dbo.CTBAtblOptions.RateID,
dbo.CTBAvSepDepC.FSADepFreqDraft,
dbo.CTBAvSepDepC.BillLevel
FROM dbo.CTBAvSepDepC LEFT OUTER JOIN
dbo.CTBAtblOptions ON
dbo.CTBAvSepDepC.BillOptionID = dbo.CTBAtblOptions.OptionID AND
dbo.CTBAvSepDepC.SAASField = dbo.CTBAtblOptions.SAASField
CREATE VIEW dbo.CTBAvSepDepCTotalDue
AS
SELECT dbo.CTBAvSepDepCRateID.LoadDate,
dbo.CTBAvSepDepCRateID.PlanSponsorNo,
dbo.CTBAvSepDepCRateID.ControlNo,
dbo.CTBAvSepDepCRateID.SuffixNo,
dbo.CTBAvSepDepCRateID.AccountNo,
dbo.CTBAvSepDepCRateID.SAASField,
dbo.CTBAvSepDepCRateID.[Count], dbo.CTBAtblRates.Rate,
dbo.CTBAvSepDepCRateID.[Count] * dbo.CTBAtblRates.Rate AS TotalDue,
dbo.CTBAvSepDepCRateID.BillLevel,
'Current Year Dependent Care Submissions' AS Description,
18 AS SortOrder
FROM dbo.CTBAtblRates RIGHT OUTER JOIN
dbo.CTBAvSepDepCRateID ON
dbo.CTBAtblRates.RateID = dbo.CTBAvSepDepCRateID.RateID AND
dbo.CTBAtblRates.Frequency = dbo.CTBAvSepDepCRateID.FSADepFreqDraft
WHERE (dbo.CTBAvSepDepCRateID.[Count] * dbo.CTBAtblRates.Rate <> 0)
KB
February 18, 2002 at 10:37 am
One thing I can suggest is to remove the <>. Since you cannot test for a non-existence without scanning everything, these tend to be inefficient. If it's only positive numbers, change to >0 or do an (<0 or >0).
Steve Jones
February 18, 2002 at 11:00 am
Might also try removing the NOT if you can. Though worth testing to see if it changes anything.
When you run these in QA, whats the response time look like? All your statistics up to date?
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply