Hi, Tim

How to speed up the following update query?

Thanks

Jianfei


UPDATE xDC_Labour M SET xSubmitted=1
WHERE EXISTS (
 SELECT * FROM xAR_LEM L
 INNER JOIN xAR_LEM_Sum S ON (S.xGUID=L.xInvoiceGUID)
 Where (L.xGUID=M.xLEMGUID) And (S.xDelete=1)
) AND (M.xUploaded=1)

Jianfei

Have you looked at the execution plan and seen what it says? It would help to answer you question if you could post the execution plan since without that all I can do is guess.

The normal problem is that the necessary indices haven't been created or you need to tell ElevateDB to use a specific COLLATION.

Roy Lambert [Team Elevate]

Hi, Roy

I am using this SQL statement directly through TEDBQuery. I have no
execution plan (BTW, what is the execute plan) for it. I just want to
optimize the SQL statement so the performance will be better.

I have many dynamic SQL statements that execute through TEDBQuery now. I
would like to find the better way to do.

Options:
 Scripts
 Stored Procedure
 or other ?

WHERE CAN I GET THE DOCUMENTS AND SAMPLES about how to use all these stuff?

I am only using local engine not the server.

Thanks

Jianfei


"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:224E5936-D664-4F6F-B17B-03F9A2EF6CC9@news.elevatesoft.com...
> Jianfei
>
> Have you looked at the execution plan and seen what it says? It would help
> to answer you question if you could post the execution plan since without
> that all I can do is guess.
>
> The normal problem is that the necessary indices haven't been created or
> you need to tell ElevateDB to use a specific COLLATION.
>
> Roy Lambert [Team Elevate]
>

Jianfei


1. When running a query you can copy and paste the sql into edbmanager and view the plan there.

2. In your app you can use RequestPlan to ask for the query to generate a plan and Plan to get the plan.

The only way you can optimise the plan is if you know where it isn't already optimised and that's what the plan is for.

Documentations and samples are what comes with ElevateDB. You can look for general books on SQL.


Roy Lambert [Team Elevate]

Jianfei,

<< How to speed up the following update query? >>

You'll need to post an execution plan (see Roy's response) or the structure
of the involved tables before anyone can give you an idea of how to improve
the performance.

You can reverse-engineer your database in the ElevateDB Manager via the
Tasks menu when you have selected your database in the left-hand treeview.
Just reverse-engineer the database as "Generate as Generic Script" and don't
include the table rows, and then post the generated SQL here.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Roy:

<< 1. When running a query you can copy and paste the sql into edbmanager and view the
plan there. >>


Can you provide more details on how to do this?  I couldn't find anything in the ElevateDB
manager regarding the execution plan.

Thanks,

Dale

Dale


When you open an sql window (not a script) the options block has a check box for request execution plan. Scripts I don't think will ever be able to generate an execution plan (it would be nifty if they could though) since they're a program rather than an sql statement.

If you have simple scripts (sort of like DBISAM) you can cut'n'paste each statement into an sql window and optimise each in turn.

Roy Lambert