Odd view performance

Odd view performance

Post by Jeff Boy » Fri, 19 Apr 2002 03:35:46



SQL Server 7.0 on NT 4.0, both at latest service packs. I am seeing some
very strange performance differences selecting from a view from two
different PC's. If I select from the view with no where clause, the full
result set is returned in ~3 seconds for both PCs. However, If I add a
simple where clause to the view, on one PC's the result set still comes back
in ~3 seconds while on the other PC it takes ~18 seconds? Both PC's are
setup to use the TCP/IP network protocol. The slow PC is at the same
location as the SQL Server while the quick PC is on the WAN, at a remote
location. If anything, I would expect the performance difference to go the
other way. Does anyone know what might be the cause of this big difference
in performance?

Thanks,
Jeff B

 
 
 

Odd view performance

Post by Keith Wong [M » Tue, 23 Apr 2002 11:37:59


Assuming data is the same, UPDATE STATS with FULLSCAN and then do the
following test:

dbcc freeproccache
go
set statistics profile on
set statistics time on
<run query>

Are the times the same? The plans are the same?

-Keith
------------------------------------------------
Please do not send email directly to this alias. Respond to newsgroup
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------


| Subject: Odd view performance
| Date: Wed, 17 Apr 2002 14:35:46 -0400
|
| SQL Server 7.0 on NT 4.0, both at latest service packs. I am seeing some
| very strange performance differences selecting from a view from two
| different PC's. If I select from the view with no where clause, the full
| result set is returned in ~3 seconds for both PCs. However, If I add a
| simple where clause to the view, on one PC's the result set still comes
back
| in ~3 seconds while on the other PC it takes ~18 seconds? Both PC's are
| setup to use the TCP/IP network protocol. The slow PC is at the same
| location as the SQL Server while the quick PC is on the WAN, at a remote
| location. If anything, I would expect the performance difference to go the
| other way. Does anyone know what might be the cause of this big difference
| in performance?
|
| Thanks,
| Jeff B
|
|
|

 
 
 

Odd view performance

Post by Jeff Boy » Tue, 23 Apr 2002 23:41:59


But from both PCs I am hitting the same SQL Server and database. The result
I mention are after consecutive runs of the same query.



> Assuming data is the same, UPDATE STATS with FULLSCAN and then do the
> following test:

> dbcc freeproccache
> go
> set statistics profile on
> set statistics time on
> <run query>

> Are the times the same? The plans are the same?

> -Keith
> ------------------------------------------------
> Please do not send email directly to this alias. Respond to newsgroup
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --------------------


> | Subject: Odd view performance
> | Date: Wed, 17 Apr 2002 14:35:46 -0400
> |
> | SQL Server 7.0 on NT 4.0, both at latest service packs. I am seeing some
> | very strange performance differences selecting from a view from two
> | different PC's. If I select from the view with no where clause, the full
> | result set is returned in ~3 seconds for both PCs. However, If I add a
> | simple where clause to the view, on one PC's the result set still comes
> back
> | in ~3 seconds while on the other PC it takes ~18 seconds? Both PC's are
> | setup to use the TCP/IP network protocol. The slow PC is at the same
> | location as the SQL Server while the quick PC is on the WAN, at a remote
> | location. If anything, I would expect the performance difference to go
the
> | other way. Does anyone know what might be the cause of this big
difference
> | in performance?
> |
> | Thanks,
> | Jeff B
> |
> |
> |

 
 
 

Odd view performance

Post by Keith Wong [M » Wed, 24 Apr 2002 12:22:23


My mistake. I thought you were testing your queries on 2 different SQL
Server 7.0 installations. Intead, you're executing the queries from 2
different SQL clients. If so, it appears to be some sort of networking
problem. Were the SQL clients Query Analyzer? If not, I recommend trying
using QA and making sure you're using the same netlib. You should be able
to use Profiler and/or SET STATISTICS TIME to how much CPU time was taken
versus elapsed time. If the main difference is in the elapsed time, then
could be a network bottleneck somewhere in which case you may need to use
NETMON to analyze the network data.

Another test you could try is to use ISQL.EXE and OSQL.EXE. This can tell
us if it's an ODBC issue.

Can't think of any other possible problems. You can possibly post this in
the .Connectivity group.

-Keith
------------------------------------------------
Please do not send email directly to this alias. Respond to newsgroup
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------



| Subject: Re: Odd view performance
| Date: Mon, 22 Apr 2002 10:41:59 -0400

|
| But from both PCs I am hitting the same SQL Server and database. The
result
| I mention are after consecutive runs of the same query.
|
|


| > Assuming data is the same, UPDATE STATS with FULLSCAN and then do the
| > following test:
| >
| > dbcc freeproccache
| > go
| > set statistics profile on
| > set statistics time on
| > <run query>
| >
| > Are the times the same? The plans are the same?
| >
| > -Keith
| > ------------------------------------------------
| > Please do not send email directly to this alias. Respond to newsgroup
| > This posting is provided "AS IS" with no warranties, and confers no

| rights.
| > --------------------


| > | Subject: Odd view performance
| > | Date: Wed, 17 Apr 2002 14:35:46 -0400
| > |
| > | SQL Server 7.0 on NT 4.0, both at latest service packs. I am seeing
some
| > | very strange performance differences selecting from a view from two
| > | different PC's. If I select from the view with no where clause, the
full
| > | result set is returned in ~3 seconds for both PCs. However, If I add a
| > | simple where clause to the view, on one PC's the result set still
comes
| > back
| > | in ~3 seconds while on the other PC it takes ~18 seconds? Both PC's
are
| > | setup to use the TCP/IP network protocol. The slow PC is at the same
| > | location as the SQL Server while the quick PC is on the WAN, at a
remote
| > | location. If anything, I would expect the performance difference to go
| the
| > | other way. Does anyone know what might be the cause of this big
| difference
| > | in performance?
| > |
| > | Thanks,
| > | Jeff B
| > |
| > |
| > |
| >
|
|
|

 
 
 

1. odd things happening between layout view and table view

I've got a database with over 100 fields made in layout view. When I
look at it in table view half of the fields are not displayed. Any
ideas why? Im suspecting its something to do with the number of fields
but I might be wrong.

2. HELP!-More MORE D3/Sybase/ODBC-EAccessViolation

3. Odd View Problem

4. Why are filters so slow?

5. Odd behavior with views and numeric columns

6. Question regarding ReportWriter of FoxPro for Window 2.5b

7. Odd Performance Issue

8. MSSQL2000 Millisecond Bug

9. Odd performance problem

10. Odd performance dropoff inside a cursor

11. Odd Performance Problem

12. odd performance

13. Odd Performance Spikes in ASE 11.9.2