Query Retrieval is slow

[maxzsim via SQLMonster.com]

Hi,

I am using MS Access as the front end and it's using ODBC link to connect
to the backend tables residing at SQL SERVER.

I have some queries that are doing some calculation and the time taken to
retrieve the data has been quite slow.

The no of data in that table is around 500K records. and the tables have
already got the PKs defined.

How can i improve the performance of the query besides converting to SQL
Views or SP(this is going to be diffcult as i am getting some filter
parameters from the forms in MS Access) ?

Or what further checks shld i be doing to find out the cause. I have done
Performance Monitor and the CPU processing% is around 20 - 55% while running
those queries

kindly advise

tks & rdgs

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200511/1

[ZeldorBlat]

>How can i improve the performance of the query besides converting to SQL

Views or SP(this is going to be diffcult as i am getting some filter
parameters from the forms in MS Access) ?

You can post your query so we know what you are trying to do.

[maxzsim via SQLMonster.com]

Hi ,
here's the query :

"INSERT INTO tb_temp ( Stock_Code, Supp_Code, Customer_code, [Group],
Obsolete ) " _
& "SELECT tb_StockCons.Stock_Code, tb_StockInfo.Supp_Code,
tb_StockCons.Customer_code, tb_StockCons.Group, tb_StockCons.Obsolete " _
& "FROM tb_StockInfo INNER JOIN tb_StockCons ON tb_StockInfo.
Stock_Code = tb_StockCons.Stock_Code AND tb_StockInfo.Cty = tb_StockCons.Cty"
_
& "WHERE (((tb_StockCons.Customer_code)=IIf([Forms]!
[frm_Consignment_Rep_Cust]![txtCustomer] Is Null,[tb_StockCons]!
[Customer_code],[Forms]![frm_Consignment_Rep_Cust]![txtCustomer])) AND
(tb_StockCons.Year between " & CheckDate([Forms]![frm_Report]![FromDate]) & "
AND " & CheckDate([Forms]![frm_Report]![ToDate]) & ") AND((tb_StockCons.
Obsolete)=IIf([Forms]![frm_Consignment_Rep_Cust]![txtObsolete] Is Null,
[tb_StockCons]![Obsolete],[Forms]![frm_Consignment_Rep_Cust]![txtObsolete]))
AND ((tb_StockInfo.Sub_Service_Category)='Product') AND ((tb_StockInfo.
Category)=IIf([Forms]![frm_Consignment_Rep_Cust]![txtCategory] Is Null,
[tb_StockInfo]![Category],[Forms]![frm_Consignment_Rep_Cust]![txtCategory])))
AND ((((tb_StockCons.Quantity" & GetPeriodRep() - 1 & ")<>0) OR (
(tb_StockCons.Amount" & GetPeriodRep() - 1 & ")<>0)));"

where GetPeriodMod & CheckDate are functions in MS Access

appreciate ur advice

tks & rdgs
ZeldorBlat wrote:

How can i improve the performance of the query besides converting to SQL
Views or SP(this is going to be diffcult as i am getting some filter
parameters from the forms in MS Access) ?

You can post your query so we know what you are trying to do.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200511/1

[VIPS]

What can be done here is to ask your system dba whether the statistics
on the tables that the query uses is updated recently. In case they are
not, ask the dba to
enable the statistics and create a job the will update statistics on a
weekend when the server is not too busy.

Another option is to use a query analyzer and see what the query plan
is like. If the plan indicates that it is using a table scan, ask have
the dba create relevant index on a table and update and set statistics
on.
On a side note, Access connection to Sql Server that you have is fairly
easy to set up however the TABLE LINKING" method is the slowest as it
hauls not just relevant records but a page amount of record at a time.

If you can do your insert using Connection in the ADO in your forms, it
would be the guaranteed fastest method without any doubt. This is
because you will bypass the virtual table in Access, and will be using
sql server indexes automatically and server processor.

[VIPS]

What can be done here is to ask your system dba whether the statistics
on the tables that the query uses is updated recently. In case they are
not, ask the dba to
enable the statistics and create a job the will update statistics on a
weekend when the server is not too busy.

Another option is to use a query analyzer and see what the query plan
is like. If the plan indicates that it is using a table scan, ask have
the dba create relevant index on a table and update and set statistics
on.
On a side note, Access connection to Sql Server that you have is fairly
easy to set up however the TABLE LINKING" method is the slowest as it
hauls not just relevant records but a page amount of record at a time.

If you can do your insert using Connection in the ADO in your forms, it
would be the guaranteed fastest method without any doubt. This is
because you will bypass the virtual table in Access, and will be using
sql server indexes automatically and server processor.

[VIPS]

What can be done here is to ask your system dba whether the statistics
on the tables that the query uses is updated recently. In case they are
not, ask the dba to
enable the statistics and create a job the will update statistics on a
weekend when the server is not too busy.

Another option is to use a query analyzer and see what the query plan
is like. If the plan indicates that it is using a table scan, ask have
the dba create relevant index on a table and update and set statistics
on.
On a side note, Access connection to Sql Server that you have is fairly
easy to set up however the TABLE LINKING" method is the slowest as it
hauls not just relevant records but a page amount of record at a time.

If you can do your insert using Connection in the ADO in your forms, it
would be the guaranteed fastest method without any doubt. This is
because you will bypass the virtual table in Access, and will be using
sql server indexes automatically and server processor.

[maxzsim via SQLMonster.com]

Hi ,
I have checked using sp_statistics 'tablename' and found that there's no
columns being declared with statistics .

How does having a statistical column(s) help to improve performance ? i
would have thought that having clustered/non-clustered index is able to
improve query performance ?

tks & rdgs

VIPS wrote:

What can be done here is to ask your system dba whether the statistics
on the tables that the query uses is updated recently. In case they are
not, ask the dba to
enable the statistics and create a job the will update statistics on a
weekend when the server is not too busy.

Another option is to use a query analyzer and see what the query plan
is like. If the plan indicates that it is using a table scan, ask have
the dba create relevant index on a table and update and set statistics
on.

On a side note, Access connection to Sql Server that you have is fairly
easy to set up however the TABLE LINKING" method is the slowest as it
hauls not just relevant records but a page amount of record at a time.

If you can do your insert using Connection in the ADO in your forms, it
would be the guaranteed fastest method without any doubt. This is
because you will bypass the virtual table in Access, and will be using
sql server indexes automatically and server processor.

--
Message posted via http://www.sqlmonster.com

[maxzsim via SQLMonster.com]

Hi ,

kindly ignore this as i found what i wanted from the BOL. I have just
found out that the query itself is quite fast after tracing thru the codes.
This query insert data into a temp table. The problem happens when the report
from MS Access is formatting the data not only from the temp table but also
SQL table.

How is it possible to create a ADO connection that can be used by ms access
report instead on relying on the odbc-linked table ?

tks & rdgs
maxzsim wrote:

Hi ,
I have checked using sp_statistics 'tablename' and found that there's no
columns being declared with statistics .

How does having a statistical column(s) help to improve performance ? i
would have thought that having clustered/non-clustered index is able to
improve query performance ?

tks & rdgs

What can be done here is to ask your system dba whether the statistics
on the tables that the query uses is updated recently. In case they are

[quoted text clipped - 15 lines]

because you will bypass the virtual table in Access, and will be using
sql server indexes automatically and server processor.

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums...neral/200511/1

[Erland Sommarskog]

maxzsim via SQLMonster.com (u14644@uwe) writes:

kindly ignore this as i found what i wanted from the BOL. I have just
found out that the query itself is quite fast after tracing thru the
codes. This query insert data into a temp table. The problem happens
when the report from MS Access is formatting the data not only from the
temp table but also SQL table.

How is it possible to create a ADO connection that can be used by ms
access report instead on relying on the odbc-linked table ?

Maybe it's time to inquire in an Access newsgroup? At least, I have
nothing to suggest, as I don't know Access.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Terry Kreft]

In Access you can use SQL Passthrough queries.

These pass the raw SQL thriough to the server which processes the request
and returns the results.

--
Terry Kreft

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1...

maxzsim via SQLMonster.com (u14644@uwe) writes:

kindly ignore this as i found what i wanted from the BOL. I have just
found out that the query itself is quite fast after tracing thru the
codes. This query insert data into a temp table. The problem happens
when the report from MS Access is formatting the data not only from the
temp table but also SQL table.

How is it possible to create a ADO connection that can be used by ms
access report instead on relying on the odbc-linked table ?

Maybe it's time to inquire in an Access newsgroup? At least, I have
nothing to suggest, as I don't know Access.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx