March 4, 2005 at 3:20 am
I have the following query i want to run:
select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END
, Amount as Amount
, st.add_datetime as [DateCreated]
from vw_site_transaction st
where site_fk in (select site_id from tblSite where cust_fk = 104536)
The view contains approximately 1010020 rows and is a union of several queries. In this example the sub query returns one value. This query takes along time to execute (>1-2mins). Site_fk is index in all tables involved in the view and on tblSite. The execution plan shows that the query is scanning the whole index (pk index) of one the tables in the view instead of scanning the site_fk index and then doing a bookmark lookup. But when I run the following query it completes in about 1 second.
select null as Cust_FK
,CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END
, Amount as Amount
, st.add_datetime as [DateCreated]
from vw_site_transaction st
where site_fk in (select 35357 from tblSite where cust_fk = 104536)
There is a different execution plan for these two queries. Is there a way to to force the query to use the second execution plan or is there any other way to to speed up the execution. I've tried index and table hints but it doesn't seem to make any difference.
March 4, 2005 at 7:50 am
I think the difference is SQL Server is smart enough to know you have a literal in your IN clause. My suggestion is to avoid the IN clause and to join the tblSite to the vw_site_transaction view and then just limit the records to cust_fk = 104536. The IN clause is not the fastest operator in SQL's bag.
c
If the phone doesn't ring...It's me.
March 4, 2005 at 8:20 am
I tried it that way to begin with but it gave me the same problem. I have tired the traditional join method where the join is specifed in the where clause and the INNER JOIN approach. It make no difference.
select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END
, Amount as Amount
, st.add_datetime as [DateCreated]
from vw_site_transaction st INNER JOIN (select site_id from tblSite where cust_fk = 104536) s
on st.site_fk = s.site_id
March 4, 2005 at 8:21 am
As sugegsted try a join, or try replacing the IN with an EXISTS:
select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END
, Amount as Amount
, st.add_datetime as [DateCreated]
from vw_site_transaction st
where exists (
Select *
from tblSite As s
where s.site_id = st.site_fk and
cust_fk = 104536
)
March 4, 2005 at 8:28 am
Thanks, but still no joy.
I could dynamically create the SQL statement by creating a cursor from, Select *
from tblSite where cust_fk = 104536 and then created the IN clause.
This seems like cheating to me but if no one has any ideas then i will have to do it that way.
March 4, 2005 at 8:31 am
Why are you using an subquery?
select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END
, Amount as Amount
, st.add_datetime as [DateCreated]
from vw_site_transaction st INNER JOIN (select site_id from tblSite where cust_fk = 104536) s
on st.site_fk = s.site_id
Try:
select CASE WHEN Invoice_FK IS NOT NULL THEN 'Invoice ' + ' Invoice_ID:' + CONVERT(VARCHAR,INvoice_fk) END
, Amount as Amount
, st.add_datetime as [DateCreated]
from vw_site_transaction st INNER JOIN tblSite on vw_site_transaction.site_id = tblsite.site_id where cust_fk = 104536
If the phone doesn't ring...It's me.
March 4, 2005 at 8:37 am
Thats what i tried original and the reason im was using a subquery is because i want SQL to execute the subquery first so that it doesn't scan the whole of vw_site_transcation. Im not sure if SQL does this but I thought i might as well give it a whirl.
March 4, 2005 at 8:41 am
My suggestion then is to add tblSite to the view in question and see what the execution plan is. You may also want to make it a stored procedure that accepts the cust_fk as a parameter then have the SQL limited to that parameter. SQL should be able to precompile it properly.
If the phone doesn't ring...It's me.
March 4, 2005 at 9:14 am
I''m sad to say i've given up using a normal query and have had to resort to dynamically created the sql statment. This returns the resultset in an instant.
DECLARE @Cust_ID INT
Set @Cust_id = 138553
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE @SQL VARCHAR(2000)
DECLARE @INClause VARCHAR(200)
DECLARE @Site_ID INT
--BUILD THE SELECT STATEMENT
SET @SQL = 'select CASE WHEN Invoice_FK IS NOT NULL THEN ' + '''' + 'INvoice ' + ''''
+ ' + ' + '''' + 'Invoice_ID:' + '''' + ' + CONVERT(VARCHAR,INvoice_fk) END
, Amount as Amount
, st.add_datetime as [DateCreated]
from vw_site_transaction st
where site_fk in ('
--BUILD THE IN CLAUSE
DECLARE c_Sites CURSOR FOR
SELECT Site_ID
FROM tblSite
WHERE Cust_fk = @Cust_id
OPEN c_Sites
FETCH NEXT FROM c_Sites INTO @Site_ID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @INClause = @INClause + CONVERT(Varchar,@Site_id) + ','
FETCH NEXT FROM c_Sites INTO @Site_ID
END
CLOSE c_Sites
DEALLOCATE c_Sites
--REMOVE TRAILING COMMA
SET @INClause = LEFT(@INClause,LEN(@INClause)-1) + ')'
--COMPLETE THE SQL STATEMENT
SET @SQL = @SQL + @INClause
execute (@sql)
March 8, 2005 at 1:25 am
Hello,
I still think it should be possible to do it without dynamic SQL, but have no other ideas than those already presented... However, if dynamic SQL will be your solution, you could avoid at least the cursor. Hopefully I didn't make any typo in the SQL, I don't have data to test it on - but we use similar statements on our tables, so it should be OK.
--BUILD THE IN CLAUSE
SET @INClause = ''
SELECT @INClause = @INClause + CONVERT(Varchar,Site_id) + ','
FROM tblSite
WHERE Cust_fk = @Cust_id
--REMOVE TRAILING COMMA
SET @INClause = LEFT(@INClause,LEN(@INClause)-1) + ')'
HTH, Vladan
March 10, 2005 at 10:23 am
I have just experience the exact same problem and came up with the same solution. But I don't think that it will be a viable solution for us... just a short term patch to a big time problem.
The strangest thing is that it seemed all the queries with this problem seemed to work not 2 days ago.
Now they are lagging indefinitely.
My view table contains ~ 10 million rows.
My lookup table contains ~ 1 million, but the restrictions quickly pair it down to < 10 rows.
I've tried forcing with an INNER LOOP JOIN to the view but it is inconsistent.
I don't know much about WA_sys indexes, but maybe they might help. Anybody with further input?
thanks,
-jmr
March 10, 2005 at 10:41 am
I eventually had to abandon the dynamic sql because we are using ADO recordsets and they don't like it when dynamic SQL or temp tables are used.
Instead a create a new view that joined each union query in the view to the site table. This seemd to sort it out. However, if the business rules change i have to remember to change both views rather than just the one.
March 10, 2005 at 10:53 am
My view doesn't actually contain any UNION clauses.
It has 20 tables.
All With JOINS OR LEFT JOINS. A few of the tables are large, most are small lookups. I feel that it should just do loop joins down the line until it gets the final recordset. (Due to a missing field it does do one book mark look up) but the end resultset is so small maybe < 10 rows that a bookmark lookup is not an issue.)
Not sure if I can effectively split it up since the key that I woul have to include in each separate view is on the biggest table anyways.
I'll have to fool with it for a while.
Thanks for the reponse.. any other ideas still welcome.
-jmr
March 11, 2005 at 8:46 am
Did you try to index the view itself?
Check "Indexed view" in BOL. It might give you significant performance gains. A lot of conditions apply to create indexed views, but they say the result worths paying...
Gabriela
March 11, 2005 at 9:04 am
The original point of the view was to encapsulate business logic, and as a result, it includes some left joins and other properties that don't allow for an indexed view.
I suppose that I'll have to break it out into a number of smaller views to take gain performance.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply