Optimizer chooses different plans

We are using Sql 2000 sp3.
All of our database access is coded in stored procedures.
When we execute a stored procedure it will do table scans, wether it is called from COM+ using ADO or executed in Query Analyzer.
If we cut and paste the code from the stored proc directly to Query Analyzer and execute it, it uses the indexes(Index Seeks).
We have added index hints and the stored proc will work for a while, but then starts doing table scans again.
An example:
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr (index=pk_mileage_rate)
LEFT JOIN EMPLOYEE e (index=uq_employee) ON
mr.union_id = e.union_id AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number AND
(mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_date)
We pass @.payroll_number and @.work_date, then return @.milage_rate.
The pk_mileage_rate index is on the columns: union_id,local_union,effective_start_date.
The uq_employee index is on the column payroll_number.
After we added the hints it worked for a while but now does table scans.
If I cut and paste this code into Query Analyzer AND remove the index hints it does Index Seeks on both tables using the indexes.
If I execute it as a stored proc it does table scans most of the time BUT index seeks sometimes.
What can we do to make our stored procs use the indexes that are there?
What can we do to make the Optimizer be consistent?
"Don" <Don@.discussions.microsoft.com> wrote in message
news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is
called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query
Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but
then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >=
@.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns:
union_id,local_union,effective_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index
hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
>
When you paste it into QA, are you removing the variables and hard-coding
the values?
Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
restriction on it? That makes no sense, and it could screw up the plan.
Try this, instead
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr
INNER JOIN EMPLOYEE e
ON mr.union_id = e.union_id
AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number
AND mr.effective_start_date <= @.work_date
AND mr.effective_end_date >= @.work_date
David
|||We are Declaring the variables and using a SELECT to set the value when we cut and paste the code to QA.
I think the code used the LEFT JOIN because there was a concern that an Employees Union may not have been entered correctly which comes from our mainframe.
All our Employee data is from the mainframe, but the mileage_rate table has no mainframe dependency.
Anyway, still doesn't answer why the code ALWAYS uses the indexes when cut and pasted to QA, but not so when the stored proc is executed.
Don
"David Browne" wrote:

> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> called from COM+ using ADO or executed in Query Analyzer.
> Analyzer and execute it, it uses the indexes(Index Seeks).
> then starts doing table scans again.
> @.work_date)
> union_id,local_union,effective_start_date.
> hints it does Index Seeks on both tables using the indexes.
> index seeks sometimes.
> When you paste it into QA, are you removing the variables and hard-coding
> the values?
> Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
> restriction on it? That makes no sense, and it could screw up the plan.
> Try this, instead
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr
> INNER JOIN EMPLOYEE e
> ON mr.union_id = e.union_id
> AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number
> AND mr.effective_start_date <= @.work_date
> AND mr.effective_end_date >= @.work_date
> David
>
>
|||Don,
Search Google for "parameter sniffing", because this is probably the
cause of your problem. It can be circumvented by not using parameters in
the query, but local variables.
For example
CREATE PROCEDURE MyProc (@.param int) AS
SELECT * FROM MyTable WHERE MyColumn = @.Param
would then become
CREATE PROCEDURE MyProc (@.param int) AS
Declare @.local int
Set @.local=@.param
SELECT * FROM MyTable WHERE MyColumn = @.local
Hope this helps,
Gert-Jan
Don wrote:
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns: union_id,local_union,effective_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
(Please reply only to the newsgroup)
sql

Optimizer chooses different plans

We are using Sql 2000 sp3.
All of our database access is coded in stored procedures.
When we execute a stored procedure it will do table scans, wether it is call
ed from COM+ using ADO or executed in Query Analyzer.
If we cut and paste the code from the stored proc directly to Query Analyzer
and execute it, it uses the indexes(Index Seeks).
We have added index hints and the stored proc will work for a while, but the
n starts doing table scans again.
An example:
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr (index=pk_mileage_rate)
LEFT JOIN EMPLOYEE e (index=uq_employee) ON
mr.union_id = e.union_id AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number AND
(mr.effective_start_date <= @.work_date AND mr.effective_end_date >= @.work_da
te)
We pass @.payroll_number and @.work_date, then return @.milage_rate.
The pk_mileage_rate index is on the columns: union_id,local_union,effective_
start_date.
The uq_employee index is on the column payroll_number.
After we added the hints it worked for a while but now does table scans.
If I cut and paste this code into Query Analyzer AND remove the index hints
it does Index Seeks on both tables using the indexes.
If I execute it as a stored proc it does table scans most of the time BUT in
dex seeks sometimes.
What can we do to make our stored procs use the indexes that are there?
What can we do to make the Optimizer be consistent?"Don" <Don@.discussions.microsoft.com> wrote in message
news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is
called from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query
Analyzer and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but
then starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >=
@.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns:
union_id,local_union,effective_start_dat
e.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index
hints it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
>
When you paste it into QA, are you removing the variables and hard-coding
the values?
Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
restriction on it? That makes no sense, and it could screw up the plan.
Try this, instead
SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
FROM MILEAGE_RATE mr
INNER JOIN EMPLOYEE e
ON mr.union_id = e.union_id
AND mr.local_union = e.local_union
WHERE e.payroll_number = @.payroll_number
AND mr.effective_start_date <= @.work_date
AND mr.effective_end_date >= @.work_date
David|||We are Declaring the variables and using a SELECT to set the value when we c
ut and paste the code to QA.
I think the code used the LEFT JOIN because there was a concern that an Empl
oyees Union may not have been entered correctly which comes from our mainfra
me.
All our Employee data is from the mainframe, but the mileage_rate table has
no mainframe dependency.
Anyway, still doesn't answer why the code ALWAYS uses the indexes when cut a
nd pasted to QA, but not so when the stored proc is executed.
Don
"David Browne" wrote:

> "Don" <Don@.discussions.microsoft.com> wrote in message
> news:CE1CAA8E-3048-4B4C-BAA8-1754AB1994EB@.microsoft.com...
> called from COM+ using ADO or executed in Query Analyzer.
> Analyzer and execute it, it uses the indexes(Index Seeks).
> then starts doing table scans again.
> @.work_date)
> union_id,local_union,effective_start_dat
e.
> hints it does Index Seeks on both tables using the indexes.
> index seeks sometimes.
> When you paste it into QA, are you removing the variables and hard-coding
> the values?
> Why are you LEFT JOINing EMPLOYEE and then applying a WHERE-clause
> restriction on it? That makes no sense, and it could screw up the plan.
> Try this, instead
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr
> INNER JOIN EMPLOYEE e
> ON mr.union_id = e.union_id
> AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number
> AND mr.effective_start_date <= @.work_date
> AND mr.effective_end_date >= @.work_date
> David
>
>|||Don,
Search Google for "parameter sniffing", because this is probably the
cause of your problem. It can be circumvented by not using parameters in
the query, but local variables.
For example
CREATE PROCEDURE MyProc (@.param int) AS
SELECT * FROM MyTable WHERE MyColumn = @.Param
would then become
CREATE PROCEDURE MyProc (@.param int) AS
Declare @.local int
Set @.local=@.param
SELECT * FROM MyTable WHERE MyColumn = @.local
Hope this helps,
Gert-Jan
Don wrote:
> We are using Sql 2000 sp3.
> All of our database access is coded in stored procedures.
> When we execute a stored procedure it will do table scans, wether it is ca
lled from COM+ using ADO or executed in Query Analyzer.
> If we cut and paste the code from the stored proc directly to Query Analyz
er and execute it, it uses the indexes(Index Seeks).
> We have added index hints and the stored proc will work for a while, but t
hen starts doing table scans again.
> An example:
> SELECT @.milage_rate = COALESCE(travel_rate_cents_per_mile, 0)
> FROM MILEAGE_RATE mr (index=pk_mileage_rate)
> LEFT JOIN EMPLOYEE e (index=uq_employee) ON
> mr.union_id = e.union_id AND mr.local_union = e.local_union
> WHERE e.payroll_number = @.payroll_number AND
> (mr.effective_start_date <= @.work_date AND mr.effective_end_date >
= @.work_date)
> We pass @.payroll_number and @.work_date, then return @.milage_rate.
> The pk_mileage_rate index is on the columns: union_id,local_union,effectiv
e_start_date.
> The uq_employee index is on the column payroll_number.
> After we added the hints it worked for a while but now does table scans.
> If I cut and paste this code into Query Analyzer AND remove the index hint
s it does Index Seeks on both tables using the indexes.
> If I execute it as a stored proc it does table scans most of the time BUT
index seeks sometimes.
> What can we do to make our stored procs use the indexes that are there?
> What can we do to make the Optimizer be consistent?
(Please reply only to the newsgroup)

optimize insert to access DB

Hi,

I'm using access tables to store my data. to retrive data I use jet odbc engine in c++.

I want to insert a large amuont of records (about 20 mega records) to my database in the fasts way i can. if i use sql syntax (insert into table ()....) it's takes for ages (about 500 records per second).

if i'm writing a csv file and then use import (via access) it's much faster but here I have two problems

1.I dont know how to use the access import tool from c++.

2.I dont think I can distributie the access import tool with my product.

so my questions are :

1. Does any know any tool that insert records in an optimize way?

2. How can I use the access import tool in c++?

3. Can i use the jet engine to import csv files?

thank ishay

This forum is dedicated to SSIS. You may get better luck asking this question in the appropriate forum.

You could use SSIS to do this, and it should bulk insert into Access using the OleDB Destination Component. You won't be able to distribute it to your users though.

Could you mark this thread as answered if you have no further questions so we can close it out? Thanks!

Optimizations for machine dedicated to processing

Hello,

We have a machine entirely dedicated to processing SSAS 2005 cubes, and then other machines sync from it and provide client access.

I notice that sometimes during processing, the processor usage hovers at about 15% and there is no paging or disk activity, which makes me wonder if SSAS is somehow limiting resources used for processing. Are there any optimizations that can be made to tell SSAS to assume that it doesn't have to serve clients, and can devote all resources to processing? I'm looking specifically at the Thread Pool / Process / Priority Ratio, which it says is an advanced property not to be changed without guidance--could increasing this work?

Thanks for any ideas.

Tom

You should be able to use several tools to monitor what is happening during processing of Analysis Services cubes:

SQL Profiler- will show you trace events
ActivityViewer sample app- will show you current connections, sessions..

Try to see what stage of processing is going slow on your machine. In several cases I have seen Analysis Server sitting and waiting for data to come from relational database.

Processing of partition has several stages:
Read Data , Write Data , Build Index ...

See which part is under suspicion.

I wouldnt go changing advanced sever properties without talking to product support.

Hope that helps.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Optimization issue

hi All,
I have a 2.5 GIG SQL2K SP3 database with an Access XP
front end application. As the database grew, the responce
time has degraded dramatically. It is now taking minutes
to sell a service, when it should only take seconds. all
together I have 20 different databases all using the same
front end. they do not seem to be having this issue.
their DB's are closer to 700 Megs.
I ran a DBCC CheckDb. there were no errors found. Are
there any ways to optimize the database? Would update
statistics, and sp_recompile help?run profiler with the default template to find the slow
query or procedure,
then paste that procedure into query analyzer
and look at the execution plan for that query
>--Original Message--
>hi All,
>I have a 2.5 GIG SQL2K SP3 database with an Access XP
>front end application. As the database grew, the
responce
>time has degraded dramatically. It is now taking minutes
>to sell a service, when it should only take seconds. all
>together I have 20 different databases all using the same
>front end. they do not seem to be having this issue.
>their DB's are closer to 700 Megs.
>I ran a DBCC CheckDb. there were no errors found. Are
>there any ways to optimize the database? Would update
>statistics, and sp_recompile help?
>
>.
>

Optimal location for SQL USR and PWD

We have created an application in VB6 where mulitple clients access a SQL
Server 2000 database. The application uses the 3tier model. We currently hav
e
20 users, but this will probably increase exponentialy. All users uses the
same SQL username and password and we handle the security in the application
.
I need advice on where and how to store the sql login username and password.
Thank you.If you are using three tiers, the safest place for the USR and PWD is in the
middle tier.
Assuming a COM+ object, you get the administrator to assign the account and
password to the middle tier. Assuming that no one has direct access to that
machine, then the password is pretty safe.
If you manage it all from the client tier, then you must store the passwords
somewhere, encrypt it somehow, and protect the knowledge of how to decrypt
it. There are several routines available out there (try GOOGLE) that you
can use for the encrypt / decrypt.
Russell Fields
"Hugo" <Hugo@.discussions.microsoft.com> wrote in message
news:7137E0FF-D18F-4FBB-B167-EB63C93FEC44@.microsoft.com...
> We have created an application in VB6 where mulitple clients access a SQL
> Server 2000 database. The application uses the 3tier model. We currently
have
> 20 users, but this will probably increase exponentialy. All users uses the
> same SQL username and password and we handle the security in the
application.
> I need advice on where and how to store the sql login username and
password.
> Thank you.
>|||We have not moved to COM+ yet. I wanted to know if there is any standard
practice for client apps.
I will now the registry with ecryption.
Thank you.