Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
query optimization

Answered query optimization

  • Friday, February 01, 2013 11:26 AM
     
     
    Sign In to Vote
    0

    I have a code in a SP as shown below(did not mention all the columns here,as there are many).Currently it is taking more time to execute.I am requested to check if i can optimize the code bit.Can any one see and let me know your thoughts.

    CREATE TABLE #temp(
     [Source] [varchar](10),
     [Currency] [varchar](3),

    )

    -- Add records into temp table
    INSERT INTO #temp
    Select Source, Currency
    From dbo.Fact Where Key = 1

    Insert Into Fact ( Source, Currency)
    Select

     Prev.Source,
     Prev.Currency

    From  
     (Select  Source, Currency) Prev
      Left Join
     #temp Curr
      On (
        Curr.Source = Prev.Source  And
        Curr.Currency = Prev.Currency  And
        )
    Where
     Curr.Source Is Null

     

All Replies

  • Friday, February 01, 2013 11:40 AM
     
      Has Code
    Sign In to Vote
    0

    Instead of using temp table you can directly do it in the query..

    Insert Into Fact ( Source, Currency)
Select

 Prev.Source,
 Prev.Currency

From  
 (Select  Source, Currency) Prev
  Left Join
 (select Source, Currency from dbo.Fact Where Key = 1) Curr
  On (
    Curr.Source = Prev.Source  And
    Curr.Currency = Prev.Currency  And
    )
Where
 Curr.Source Is Null

    sarat chandra sahoo

     
  • Friday, February 01, 2013 12:09 PM
     
     Proposed Answer
    Sign In to Vote
    0

    I think ditching the temp table like Ashwin AC suggested is a good first step.  But the query you posted has is not correct in that there is no FROM clause for the Prev source, which may be an important detail to correctly answer your question.  You might refactore to use NOT EXISTS instead.  For performance, it is important to have a composite index on Source and Currency on these tables (ideally clustered and unique).

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

     
  • Friday, February 01, 2013 2:00 PM
    Moderator
     
     
    Sign In to Vote
    0

    It is helpful if you post script & DDL so we can test.

    The following article is on optimization:

    http://www.sqlusa.com/articles/query-optimization/

    Kalman Toth SQL 2008 GRAND SLAM
    Paperback/Kindle: SQL Server 2012 Pro


     
  • Monday, February 04, 2013 12:49 PM
     
     
    Sign In to Vote
    0

    Hi Dan Guzman,

    Thanks for the reply.The inner query uses another table (say table2).

    CREATE TABLE #temp(
     [Source] [varchar](10),
     [Currency] [varchar](3),

    )

    -- Add records into temp table
    INSERT INTO #temp
    Select Source, Currency
    From dbo.Fact Where Key = 1

    Insert Into Fact ( Source, Currency)
    Select

     Prev.Source,
     Prev.Currency

    From  
     (Select  Source, Currency from table2) Prev
      Left Join
     #temp Curr
      On (
        Curr.Source = Prev.Source  And
        Curr.Currency = Prev.Currency  And
        )
    Where
     Curr.Source Is Null

    <input id="35eebc08-2977-4d9c-8020-7e2e30a6a394_attachments" type="hidden" />
     
  • Tuesday, February 05, 2013 12:14 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Below are a couple of methods without the temp table.  A composite (unique?) index on dbo.Fact columns Key, Source and Currency will help optimize these queries.

    INSERT INTO dbo.Fact ( Source, Currency )
SELECT
	Prev.Source
	,Prev.Currency
FROM  dbo.table2 AS Prev
LEFT JOIN (SELECT Source, Currency 
	FROM dbo.Fact
	WHERE [Key] = 1) AS Curr ON
     Curr.Source = Prev.Source
     AND Curr.Currency = Prev.Currency
WHERE
	Curr.Source IS NULL;

INSERT INTO dbo.Fact ( Source, Currency )
SELECT
	Prev.Source
	,Prev.Currency
FROM  dbo.table2 AS Prev
WHERE NOT EXISTS(
	SELECT *
	FROM dbo.Fact AS Curr
	WHERE 
		[Key] = 1
		AND Curr.Source = Prev.Source
		AND Curr.Currency = Prev.Currency
	);

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

     
  • Tuesday, February 05, 2013 2:09 PM
     
     
    Sign In to Vote
    0

    1) Select Source, Currency
        From dbo.Fact Where Key = 1

        Here I would try to use filtered Index

    2) In fact I would try to use IndexedView instead of this code

    Execution plan should be investigated