sql query taking too long

Question

Hi,

I have simple "insert into.." query which is taking around 40 seconds to execute. It simply takes records from one table and inserts into another.

1. I have index on F1 and BatchID on the tbl_TempCatalogue table
2. 146624 records are effected.
3. select itself is not slow insert into is slow.

The complete query is

insert into tbl_ItemPrice
    (CATALOGUEVERSIONID,SERIESNUMBER,TYPE,PRICEFIELD,PRICE,
PRICEONREQUEST,recordid)
select  296 as CATALOGUEVERSIONID
        ,ISNULL(F2,'-32768') as SERIESNUMBER
        ,ISNULL(F3,'-32768') as TYPE
        ,ISNULL(F4,'-32768') as PRICEFIELD,F5 as PRICE
       ,(case  when F6 IS NULL  then null when F6 = '0' then 'False' 
                                else 'True' end ) as PRICEONREQUEST
       ,newid()
 from tbl_TempCatalogue  
 where F1 = 450
  and BATCHID = 72

Thanks, Lalit

Answer 1

It's possible that if your table is large, you'd benefit from indexes on F1 and BATCHID in the tbl_TempCatalogue table. It's not clear what DBMS you're using, but most have decent tools to show you an execution plan. If you're doing full table scans on a large table, that may take a long time to run.

Also, you say that the "insert into" is slow, but you include just the code for the select. Is the select slow by itself?

Answer 2

Is there an index on the tbl_TempCatalogue table to help the database find the rows where F1=450 and BATCHID=72?

Otherwise, it will probably need to scan the entire table to find them.

Answer 3

Your query looks fine ..but my concern is in newid() function , what logic written there that may hit the performance, plese try to run without newid() and see the execution time of select statement..

to solve such type of issue follow the steps

1. see execution time only for select statement
2. see execution time of insert statement with select
3. see execution time for select statement without newid() function

after comparing those time slots you will be able to locate the exact root cause that where the problem reside..after that please post that time slots so that we will try to sole this issue..

Answer 4

You say the problem lies with the INSERT not the SELECT. So possible culprits are (in no fixed order):

• triggers
• storage allocation
• foreign key validation
• check constraint validation
• i/o bottlenecks
• faulty disk
• contention with other sessions

What tools you have to undertake diagnosis will depend on which database product (and which version of which database) you are using. Please include these details in your question.