Help: Area SQL-query times goods? 
Author Message
 Help: Area SQL-query times goods?

I've got all my Information system on some MS-Access 7.0 Databases.
Some databases are 250 Mb. Then i asked to a System Engieniering from
Microsoft about it and he told me i update to MS-SQL Server.

I bought it (A 100 Mhz. Pentium Server, 80 Mb RAM, 4Gb. HD and NT 3.51/SQL
6.0) and begun to test it. (I havent access to my server because i have an
outsourcing service. They installed and configured it).

When i test it, i obtain the next results:

Query: Create a Table from two tables (4496 registers/274 K and 114035
regs./13 Mb.).
They are relationed by a field (with index in both), and the result are by
group and sum.
The result table are a 24786 regs.
The execution time (only one user, me) is up 30 min.

When i replic the test in MS-Access (the same tables and query, but in MDB
format), i obtain the same result in 1 min. 14 sec.

Is this usual or OK? Has the SQL time be lower than Access?

Thank you.

J.C.



Fri, 05 Nov 1999 03:00:00 GMT
 Help: Area SQL-query times goods?

Let's post this one in .server, to start with....

But to answer your question, did you index properly in sqlserver?  How big
is tempdb?  How much memory on the server is dedicated to sqlserver?
--
Richard Waymire, MCT, MCSE, MVP (SQLServer)
Principal Engineer, ARIS Corp.
President, Pacific NW SqlServer User Group - http://www.pnwssug.org
Visit us at http://www.aris.com



Quote:
> I've got all my Information system on some MS-Access 7.0 Databases.
> Some databases are 250 Mb. Then i asked to a System Engieniering from
> Microsoft about it and he told me i update to MS-SQL Server.

> I bought it (A 100 Mhz. Pentium Server, 80 Mb RAM, 4Gb. HD and NT
3.51/SQL
> 6.0) and begun to test it. (I havent access to my server because i have
an
> outsourcing service. They installed and configured it).

> When i test it, i obtain the next results:

> Query: Create a Table from two tables (4496 registers/274 K and 114035
> regs./13 Mb.).
> They are relationed by a field (with index in both), and the result are
by
> group and sum.
> The result table are a 24786 regs.
> The execution time (only one user, me) is up 30 min.

> When i replic the test in MS-Access (the same tables and query, but in
MDB
> format), i obtain the same result in 1 min. 14 sec.

> Is this usual or OK? Has the SQL time be lower than Access?

> Thank you.

> J.C.



Fri, 05 Nov 1999 03:00:00 GMT
 Help: Area SQL-query times goods?

Este es un mensaje con mltiples partes en formato MIME.

------=_NextPart_000_01BC3257.E1E8B880
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit



Quote:
> I've got all my Information system on some MS-Access 7.0 Databases.
> Some databases are 250 Mb. Then i asked to a System Engieniering from
> Microsoft about it and he told me i update to MS-SQL Server.

> I bought it (A 100 Mhz. Pentium Server, 80 Mb RAM, 4Gb. HD and NT
3.51/SQL
> 6.0) and begun to test it. (I havent access to my server because i have
an
> outsourcing service. They installed and configured it).

> When i test it, i obtain the next results:

> Query: Create a Table from two tables (4496 registers/274 K and 114035
> regs./13 Mb.).
> They are relationed by a field (with index in both), and the result are
by
> group and sum.
> The result table are a 24786 regs.
> The execution time (only one user, me) is up 30 min.

> When i replic the test in MS-Access (the same tables and query, but in
MDB
> format), i obtain the same result in 1 min. 14 sec.

> Is this usual or OK? Has the SQL time be lower than Access?

> Thank you.

> J.C.

Jose

If you are using Access with a standard query, there are a problem with
your approach. The problem is simple, the JET engine is messing with your
query. I've seen this problem many times and makes people think of SQL
Server performance sloggy just because of a lack of understanding of how
Access works with an ODBC data source.

Unless the JET engine is clever enough (and in 99% of cases it is not),
it'll perform the operation at least partially at the client, thus, making,
at a minimun, travel 24768 records (the result set from which you are going
to create the table) from the  server to the client and back again at the
server to create the table. Thus, what are you seeing is NOT degradation of
performance due to the use of SQL server, but the overhead imposed by the
network connection to SQL server and the ODBC calls needed to accomplish
the whole thing. The solution?. Leave JET out of the equation and treat the
ODBC connection as a low-bandwidth link between client and server. Always
try to do the maximum amount of work at the server (that's why you
upgraded)

Alfonso

------=_NextPart_000_01BC3257.E1E8B880
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head></head><BODY bgcolor=3D"#FFFFFF"><p><font size=3D2 =
color=3D"#000000" face=3D"Arial"><br>Jose Gutierrez &lt;<font =

escribi=F3 en art=EDculo &lt;<font =

color=3D"#000000">&gt;...<br>&gt; I've got all my Information system on =
some MS-Access 7.0 Databases.<br>&gt; Some databases are 250 Mb. Then i =
asked to a System Engieniering from<br>&gt; Microsoft about it and he =
told me i update to MS-SQL Server.<br>&gt; <br>&gt; I bought it (A 100 =
Mhz. Pentium Server, 80 Mb RAM, 4Gb. HD and NT 3.51/SQL<br>&gt; 6.0) and =
begun to test it. (I haven=B4t access to my server because i have =
an<br>&gt; outsourcing service. They installed and configured =
it).<br>&gt; <br>&gt; When i test it, i obtain the next results:<br>&gt; =
<br>&gt; Query: Create a Table from two tables (4496 registers/274 K and =
114035<br>&gt; regs./13 Mb.).<br>&gt; They are relationed by a field =
(with index in both), and the result are by<br>&gt; group and =
sum.<br>&gt; The result table are a 24786 regs.<br>&gt; The execution =
time (only one user, me) is up 30 min.<br>&gt; <br>&gt; When i replic =
the test in MS-Access (the same tables and query, but in MDB<br>&gt; =
format), i obtain the same result in 1 min. 14 sec.<br>&gt; <br>&gt; Is =
this usual or OK? Has the SQL time be lower than Access?<br>&gt; =
<br>&gt; Thank you.<br>&gt; <br>&gt; J.C.<br>&gt; <br><br>Jose<br><br>If =
you are using Access with a standard query, there are a problem with =
your approach. The problem is simple, the JET engine is messing with =
your query. I've seen this problem many times and makes people think of =
SQL Server performance sloggy just because of a lack of understanding of =
how Access works with an ODBC data source.<br><br>Unless the JET engine =
is clever enough (and in 99% of cases it is not), it'll perform the =
operation at least partially at the client, thus, making, at a minimun, =
travel 24768 records (the result set from which you are going to create =
the table) from the &nbsp;server to the client and back again at the =
server to create the table. Thus, what are you seeing is NOT degradation =
of performance due to the use of SQL server, but the overhead imposed by =
the network connection to SQL server and the ODBC calls needed to =
accomplish the whole thing. The solution?. Leave JET out of the equation =
and treat the ODBC connection as a low-bandwidth link between client and =
server. Always try to do the maximum amount of work at the server =
(that's why you upgraded)<br><br>Alfonso<br><br></p>
</font></font></font></font></font></body></html>
------=_NextPart_000_01BC3257.E1E8B880--



Thu, 18 Nov 1999 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Help:SQL Query using a Date/time variable

2. goods books on PowerDesigner and MS SQL Server

3. Help, get values from another table column 3 times in one query

4. same query speed varies dramatically from time to time

5. query time outs! please help

6. help with query execution time problem

7. Mental block -- Time based Query help.

8. Time Query problem...please help

9. Query - Date/Time field - HELP?

10. help: a complex query (with sample data this time)

11. same query speed varies dramatically from time to time

12. Processing Takes Too Time (How to reduse query time)


 
Powered by phpBB® Forum Software