SQl Data Provider Performance Issues

G

Greg

I am trying to fill strongly typed datasets with data from a SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts, updates,
or deletes. When I call the Fill method to load the dataset, CPU Usage
goes to 100%. These simple queries take forever to complete. We are
only dealing with a few hundred rows. My windows form takes minutes to
load because of the poor performance. The select statements require 2
inner joins to aquire the required data columns. Similar fills using
selects with a single inner join perforn OK. I tried running the SQl
Profiler to analyze the problem. When I run the query from SQL Query
Analyzer, SQL Profiler shows a duration of 30. When run from withing
the C# .net application duration jumps to 9000 or 300 times slower.
Reads stay the same. SQL execution plan is the same when using Query
analyzer versus the application. It appears the SQL Data Provider is
the source of the poor performance. Here is the SQL Select that
performs poorly:

SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID = L.HLALowResID
WHERE (C.CategoryName = 'A')

Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]), SEEK:([L].[HLALowResID]=[H].
[HLALowResID]) ORDERED FORWARD)




Here is the SQL Select that performs OK even though duration doubles:

SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName

Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))


Thes are quite similar excpet for an extra INNER JOIN. What can the
souce of this poor perfomance be? Why does CPU get pegged at 100% when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?
 
G

Guest

This sounds really silly but I've had the same issue and after banging my
head against a virtual wall for a couple of hours a reboot seemed to do the
trick. Go figure.
 
M

Mr. Arnold

Thes are quite similar excpet for an extra INNER JOIN. What can the
souce of this poor perfomance be? Why does CPU get pegged at 100% when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?
Click to expand...

<copied from link>

One of the reasons the SQL .NET Data Provider has gotten so much hype (which
is why all the samples use it), is how it has been optimized. The SQL
Managed Provider talks directly to SQL Server without using OLEDB (the
benefits of using products from the same company). Microsoft claims that the
speed of moving data between SQL Server and your ASP.NET application can
increase as much as 300% or more using the SQL Managed Provider because of
this direct communication.

<end>

http://dotnetjunkies.com/Tutorial/926E56FB-3E8B-4A9B-A872-E5D7C89364A1.dcik

When I was taking my .Net training a couple of yeas ago, it was explained
that for
speed considerations to avoid OleDb if you were concerned about speed in
accessing SQL Server.

Secondly, I don't like using datasets for anything. I would much rather use
a strong typed collection of objects populated by the database and bound to
a control.
 
N

Nicholas Paldino [.NET/C# MVP]

Greg,

It seems like something else is definitely going on here. I can't see
the code you are using, so I can't tell what is going on on the .NET side.
However, from what you have shown of the execution tree, it seems like you
haven't optimized the ValidValues_HLAHighRes table for this query. It's
doing an index scan, which is not really what you want. You should probably
index it so that an index seek will be performed. If you have a large
number of records in the ValidValues_HLAHighRes table, then that could be
impacting performance.

Of course, there is some overhead in populating a data set, but from
what you are mentioning, it doesn't seem like it should be that much.
Assuming a reasonable number of columns in the table, a few hundred rows
really shouldn't take that long.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Greg said:
I am trying to fill strongly typed datasets with data from a SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts, updates,
or deletes. When I call the Fill method to load the dataset, CPU Usage
goes to 100%. These simple queries take forever to complete. We are
only dealing with a few hundred rows. My windows form takes minutes to
load because of the poor performance. The select statements require 2
inner joins to aquire the required data columns. Similar fills using
selects with a single inner join perforn OK. I tried running the SQl
Profiler to analyze the problem. When I run the query from SQL Query
Analyzer, SQL Profiler shows a duration of 30. When run from withing
the C# .net application duration jumps to 9000 or 300 times slower.
Reads stay the same. SQL execution plan is the same when using Query
analyzer versus the application. It appears the SQL Data Provider is
the source of the poor performance. Here is the SQL Select that
performs poorly:

SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID = L.HLALowResID
WHERE (C.CategoryName = 'A')

Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]), SEEK:([L].[HLALowResID]=[H].
[HLALowResID]) ORDERED FORWARD)




Here is the SQL Select that performs OK even though duration doubles:

SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName

Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))


Thes are quite similar excpet for an extra INNER JOIN. What can the
souce of this poor perfomance be? Why does CPU get pegged at 100% when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?
Click to expand...
 
G

Guest

TD said:
This sounds really silly but I've had the same issue and after banging my
head against a virtual wall for a couple of hours a reboot seemed to do the
trick. Go figure.
Click to expand...

I would not call that a solution.

Problems that disappear with a reboot often come back again later.

Arne
 
?

=?ISO-8859-1?Q?Arne_Vajh=F8j?=

Mr. Arnold said:
One of the reasons the SQL .NET Data Provider has gotten so much hype
(which
is why all the samples use it), is how it has been optimized. The SQL
Managed Provider talks directly to SQL Server without using OLEDB (the
benefits of using products from the same company). Microsoft claims that
the
speed of moving data between SQL Server and your ASP.NET application can
increase as much as 300% or more using the SQL Managed Provider because of
this direct communication.
Click to expand...

I posted the results of a little test here back in June:

SQL Client (2.0): INSERT 20000 rows: 9,109375 seconds
SQL Client (2.0): 50 SELECT 20000 rows: 1,75 seconds
OLE DB (2000): INSERT 20000 rows: 20 seconds
OLE DB (2000): 50 SELECT 20000 rows: 62,140625 seconds
ODBC (2000): INSERT 20000 rows: 16,65625 seconds
ODBC (2000): 50 SELECT 20000 rows: 47,8125 seconds
OLE DB (2005): INSERT 20000 rows: 17,28125 seconds
OLE DB (2005): 50 SELECT 20000 rows: 61,484375 seconds
ODBC (2005): INSERT 20000 rows: 13,96875 seconds
ODBC (2005): 50 SELECT 20000 rows: 47,734375 seconds

Arne
 
G

Greg

Greg,

It seems like something else is definitely going on here. I can't see
the code you are using, so I can't tell what is going on on the .NET side.
However, from what you have shown of the execution tree, it seems like you
haven't optimized the ValidValues_HLAHighRes table for this query. It's
doing an index scan, which is not really what you want. You should probably
index it so that an index seek will be performed. If you have a large
number of records in the ValidValues_HLAHighRes table, then that could be
impacting performance.

Of course, there is some overhead in populating a data set, but from
what you are mentioning, it doesn't seem like it should be that much.
Assuming a reasonable number of columns in the table, a few hundred rows
really shouldn't take that long.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)




I am trying to fill strongly typed datasets with data from a SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts, updates,
or deletes. When I call the Fill method to load the dataset, CPU Usage
goes to 100%. These simple queries take forever to complete. We are
only dealing with a few hundred rows. My windows form takes minutes to
load because of the poor performance. The select statements require 2
inner joins to aquire the required data columns. Similar fills using
selects with a single inner join perforn OK. I tried running the SQl
Profiler to analyze the problem. When I run the query from SQL Query
Analyzer, SQL Profiler shows a duration of 30. When run from withing
the C# .net application duration jumps to 9000 or 300 times slower.
Reads stay the same. SQL execution plan is the same when using Query
analyzer versus the application. It appears the SQL Data Provider is
the source of the poor performance. Here is the SQL Select that
performs poorly:
Click to expand...
SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID = L.HLALowResID
WHERE (C.CategoryName = 'A')
Click to expand...
Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]), SEEK:([L].[HLALowResID]=[H].
[HLALowResID]) ORDERED FORWARD)
Click to expand...
Here is the SQL Select that performs OK even though duration doubles:
Click to expand...
SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName
Click to expand...
Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))
Click to expand...
Thes are quite similar excpet for an extra INNER JOIN. What can the
souce of this poor perfomance be? Why does CPU get pegged at 100% when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The HLAHighRes table has 3600 rows, HLALowRes has 140 rows, And
HLACategories has just 6 rows.
The queries run very fast from query analyzer but get terribly slow
when called by the data adapter fill method.
CPU goes to 100%. In my DB experience it performs like it is doing
full table scans with thousands of rows.
All the tables are keyed by an identity column hence the ID in the
column name and have a primary clustered key index.
I tried adding an index on the combo of H.HLAHighResID, H.HighResName,
H.HLALowResID
which was recommended by the profiler index analyzer. It really did
not effect performance.
Seems to me there is some issue with the interaction of the app and
DB.
 
G

Greg

I posted the results of a little test here back in June:

SQL Client (2.0): INSERT 20000 rows: 9,109375 seconds
SQL Client (2.0): 50 SELECT 20000 rows: 1,75 seconds
OLE DB (2000): INSERT 20000 rows: 20 seconds
OLE DB (2000): 50 SELECT 20000 rows: 62,140625 seconds
ODBC (2000): INSERT 20000 rows: 16,65625 seconds
ODBC (2000): 50 SELECT 20000 rows: 47,8125 seconds
OLE DB (2005): INSERT 20000 rows: 17,28125 seconds
OLE DB (2005): 50 SELECT 20000 rows: 61,484375 seconds
ODBC (2005): INSERT 20000 rows: 13,96875 seconds
ODBC (2005): 50 SELECT 20000 rows: 47,734375 seconds

Arne
Click to expand...

OK so OLE or ODBC is not going to make things any better.
 
G

Greg

I posted the results of a little test here back in June:

SQL Client (2.0): INSERT 20000 rows: 9,109375 seconds
SQL Client (2.0): 50 SELECT 20000 rows: 1,75 seconds
OLE DB (2000): INSERT 20000 rows: 20 seconds
OLE DB (2000): 50 SELECT 20000 rows: 62,140625 seconds
ODBC (2000): INSERT 20000 rows: 16,65625 seconds
ODBC (2000): 50 SELECT 20000 rows: 47,8125 seconds
OLE DB (2005): INSERT 20000 rows: 17,28125 seconds
OLE DB (2005): 50 SELECT 20000 rows: 61,484375 seconds
ODBC (2005): INSERT 20000 rows: 13,96875 seconds
ODBC (2005): 50 SELECT 20000 rows: 47,734375 seconds

Arne
Click to expand...

The Code is in the form load method and is vanilla datadapter fills.
It is the very first
thing I do whjen the form is loaded, fill the datasets for use in drop
down lists:

PtDNAA1LUDA.Fill(PtDNAA1LUDS);
PtDNAA2LUDA.Fill(PtDNAA2LUDS);
PtDNAB1LUDA.Fill(PtDNAB1LUDS);
PtDNAB2LUDA.Fill(PtDNAB2LUDS);
PtDNACw1LUDA.Fill(PtDNACw1LUDS);
PtDNACw2LUDA.Fill(PtDNACw2LUDS);
PtDNADQ1LUDA.Fill(PtDNADQ1LUDS);
PtDNADQ2LUDA.Fill(PtDNADQ2LUDS);
PtDNADR1LUDA.Fill(PtDNADR1LUDS);
PtDNADR2LUDA.Fill(PtDNADR2LUDS);
 
G

Greg

OK so OLE or ODBC is not going to make things any better.- Hide quoted text -

- Show quoted text -
Click to expand...

I have had similar issues in other forms that have data adapters with
complex select statements. The commonality seems to be that having
more than 1 INNER join in a select statement causes a performance
issue in the app. The app uses a lot of data adapters and datasets in
each windows form. This particular form has 42 data adapters and
associated datasets. One thought I had was to use a sub-select in
place of the INNER JOINS.
 
M

Mr. Arnold

I have had similar issues in other forms that have data adapters with
complex select statements. The commonality seems to be that having
more than 1 INNER join in a select statement causes a performance
issue in the app. The app uses a lot of data adapters and datasets in
each windows form. This particular form has 42 data adapters and
associated datasets. One thought I had was to use a sub-select in
place of the INNER JOINS.

42 data adapters and datasets what a nightmare?
 
N

Nicholas Paldino [.NET/C# MVP]

Based on your other posts, it seems like it's not so much the individual
fills, but rather, the fact that you have so many of them. Are the numbers
you gave for one individual call to Fill the data set, or is it for all of
them?

I believe you are on the right track when you say that it is the
interaction of the app with the database, but you haven't given many details
about that. I don't think it is the SqlDataAdapter in itself. Have you
tried just executing the command, getting a reader, and then cycling through
it (columns and rows) to see what the performance there is? Or, maybe just
filling one dataset, and measuring the performance there?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Greg said:
Greg,

It seems like something else is definitely going on here. I can't
see
the code you are using, so I can't tell what is going on on the .NET
side.
However, from what you have shown of the execution tree, it seems like
you
haven't optimized the ValidValues_HLAHighRes table for this query. It's
doing an index scan, which is not really what you want. You should
probably
index it so that an index seek will be performed. If you have a large
number of records in the ValidValues_HLAHighRes table, then that could be
impacting performance.

Of course, there is some overhead in populating a data set, but from
what you are mentioning, it doesn't seem like it should be that much.
Assuming a reasonable number of columns in the table, a few hundred rows
really shouldn't take that long.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)




I am trying to fill strongly typed datasets with data from a SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts, updates,
or deletes. When I call the Fill method to load the dataset, CPU Usage
goes to 100%. These simple queries take forever to complete. We are
only dealing with a few hundred rows. My windows form takes minutes to
load because of the poor performance. The select statements require 2
inner joins to aquire the required data columns. Similar fills using
selects with a single inner join perforn OK. I tried running the SQl
Profiler to analyze the problem. When I run the query from SQL Query
Analyzer, SQL Profiler shows a duration of 30. When run from withing
the C# .net application duration jumps to 9000 or 300 times slower.
Reads stay the same. SQL execution plan is the same when using Query
analyzer versus the application. It appears the SQL Data Provider is
the source of the poor performance. Here is the SQL Select that
performs poorly:
Click to expand...
SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID = L.HLALowResID
WHERE (C.CategoryName = 'A')
Click to expand...
Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]), SEEK:([L].[HLALowResID]=[H].
[HLALowResID]) ORDERED FORWARD)
Click to expand...
Here is the SQL Select that performs OK even though duration doubles:
Click to expand...
SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName
Click to expand...
Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))
Click to expand...
Thes are quite similar excpet for an extra INNER JOIN. What can the
souce of this poor perfomance be? Why does CPU get pegged at 100% when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

The HLAHighRes table has 3600 rows, HLALowRes has 140 rows, And
HLACategories has just 6 rows.
The queries run very fast from query analyzer but get terribly slow
when called by the data adapter fill method.
CPU goes to 100%. In my DB experience it performs like it is doing
full table scans with thousands of rows.
All the tables are keyed by an identity column hence the ID in the
column name and have a primary clustered key index.
I tried adding an index on the combo of H.HLAHighResID, H.HighResName,
H.HLALowResID
which was recommended by the profiler index analyzer. It really did
not effect performance.
Seems to me there is some issue with the interaction of the app and
DB.
Click to expand...
 
G

Greg

Based on your other posts, it seems like it's not so much the individual
fills, but rather, the fact that you have so many of them. Are the numbers
you gave for one individual call to Fill the data set, or is it for all of
them?

I believe you are on the right track when you say that it is the
interaction of the app with the database, but you haven't given many details
about that. I don't think it is the SqlDataAdapter in itself. Have you
tried just executing the command, getting a reader, and then cycling through
it (columns and rows) to see what the performance there is? Or, maybe just
filling one dataset, and measuring the performance there?

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)




Greg,
It seems like something else is definitely going on here. I can't
see
the code you are using, so I can't tell what is going on on the .NET
side.
However, from what you have shown of the execution tree, it seems like
you
haven't optimized the ValidValues_HLAHighRes table for this query. It's
doing an index scan, which is not really what you want. You should
probably
index it so that an index seek will be performed. If you have a large
number of records in the ValidValues_HLAHighRes table, then that could be
impacting performance.
Of course, there is some overhead in populating a data set, but from
what you are mentioning, it doesn't seem like it should be that much.
Assuming a reasonable number of columns in the table, a few hundred rows
really shouldn't take that long.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I am trying to fill strongly typed datasets with data from a SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts, updates,
or deletes. When I call the Fill method to load the dataset, CPU Usage
goes to 100%. These simple queries take forever to complete. We are
only dealing with a few hundred rows. My windows form takes minutes to
load because of the poor performance. The select statements require 2
inner joins to aquire the required data columns. Similar fills using
selects with a single inner join perforn OK. I tried running the SQl
Profiler to analyze the problem. When I run the query from SQL Query
Analyzer, SQL Profiler shows a duration of 30. When run from withing
the C# .net application duration jumps to 9000 or 300 times slower.
Reads stay the same. SQL execution plan is the same when using Query
analyzer versus the application. It appears the SQL Data Provider is
the source of the poor performance. Here is the SQL Select that
performs poorly:
SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID = L.HLALowResID
WHERE (C.CategoryName = 'A')
Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]), SEEK:([L].[HLALowResID]=[H].
[HLALowResID]) ORDERED FORWARD)
Here is the SQL Select that performs OK even though duration doubles:
SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName
Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))
Thes are quite similar excpet for an extra INNER JOIN. What can the
souce of this poor perfomance be? Why does CPU get pegged at 100% when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
The HLAHighRes table has 3600 rows, HLALowRes has 140 rows, And
HLACategories has just 6 rows.
The queries run very fast from query analyzer but get terribly slow
when called by the data adapter fill method.
CPU goes to 100%. In my DB experience it performs like it is doing
full table scans with thousands of rows.
All the tables are keyed by an identity column hence the ID in the
column name and have a primary clustered key index.
I tried adding an index on the combo of H.HLAHighResID, H.HighResName,
H.HLALowResID
which was recommended by the profiler index analyzer. It really did
not effect performance.
Seems to me there is some issue with the interaction of the app and
DB.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

40 of the datasets get filled when the form loads. The 1st 10 that use
1 INNER JOIN load OK.
As soon as it hits the next 10 that have 2 INNER Joins performance
goes bad. Then the next 10 datasets have
the 1 INNER JOIN again and performance picks up. The last 10 datasets
filled are back to 2 INNER JOINS and performance goes bad again.
I have a Profiler Trace showing this. I tried changing the order by
putting the poor performers 1st. It did not matter.
The performance was still going from poor to good depending on the
query and not the order.
 
G

Greg

Based on your other posts, it seems like it's not so much the individual
fills, but rather, the fact that you have so many of them. Are the numbers
you gave for one individual call to Fill the data set, or is it for all of
them?
Click to expand...
I believe you are on the right track when you say that it is the
interaction of the app with the database, but you haven't given many details
about that. I don't think it is the SqlDataAdapter in itself. Have you
tried just executing the command, getting a reader, and then cycling through
it (columns and rows) to see what the performance there is? Or, maybe just
filling one dataset, and measuring the performance there?
Click to expand...
news:[email protected]...
Click to expand...
On Aug 6, 7:36 pm, "Nicholas Paldino [.NET/C# MVP]"
Greg,
It seems like something else is definitely going on here. I can't
see
the code you are using, so I can't tell what is going on on the .NET
side.
However, from what you have shown of the execution tree, it seems like
you
haven't optimized the ValidValues_HLAHighRes table for this query. It's
doing an index scan, which is not really what you want. You should
probably
index it so that an index seek will be performed. If you have a large
number of records in the ValidValues_HLAHighRes table, then that could be
impacting performance.
Of course, there is some overhead in populating a data set, but from
what you are mentioning, it doesn't seem like it should be that much.
Assuming a reasonable number of columns in the table, a few hundred rows
really shouldn't take that long.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I am trying to fill strongly typed datasets with data from a SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts, updates,
or deletes. When I call the Fill method to load the dataset, CPU Usage
goes to 100%. These simple queries take forever to complete. We are
only dealing with a few hundred rows. My windows form takes minutes to
load because of the poor performance. The select statements require 2
inner joins to aquire the required data columns. Similar fills using
selects with a single inner join perforn OK. I tried running the SQl
Profiler to analyze the problem. When I run the query from SQL Query
Analyzer, SQL Profiler shows a duration of 30. When run from withing
the C# .net application duration jumps to 9000 or 300 times slower.
Reads stay the same. SQL execution plan is the same when using Query
analyzer versus the application. It appears the SQL Data Provider is
the source of the poor performance. Here is the SQL Select that
performs poorly:
SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID = L.HLALowResID
WHERE (C.CategoryName = 'A')
Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]), SEEK:([L].[HLALowResID]=[H].
[HLALowResID]) ORDERED FORWARD)
Here is the SQL Select that performs OK even though duration doubles:
SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName
Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))
Thes are quite similar excpet for an extra INNER JOIN. What can the
souce of this poor perfomance be? Why does CPU get pegged at 100% when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?- Hide quoted text -
- Show quoted text -
The HLAHighRes table has 3600 rows, HLALowRes has 140 rows, And
HLACategories has just 6 rows.
The queries run very fast from query analyzer but get terribly slow
when called by the data adapter fill method.
CPU goes to 100%. In my DB experience it performs like it is doing
full table scans with thousands of rows.
All the tables are keyed by an identity column hence the ID in the
column name and have a primary clustered key index.
I tried adding an index on the combo of H.HLAHighResID, H.HighResName,
H.HLALowResID
which was recommended by the profiler index analyzer. It really did
not effect performance.
Seems to me there is some issue with the interaction of the app and
DB.- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

40 of the datasets get filled when the form loads. The 1st 10 that use
1 INNER JOIN load OK.
As soon as it hits the next 10 that have 2 INNER Joins performance
goes bad. Then the next 10 datasets have
the 1 INNER JOIN again and performance picks up. The last 10 datasets
filled are back to 2 INNER JOINS and performance goes bad again.
I have a Profiler Trace showing this. I tried changing the order by
putting the poor performers 1st. It did not matter.
The performance was still going from poor to good depending on the
query and not the order.- Hide quoted text -

- Show quoted text -
Click to expand...

The numbers I gave were for 1 dataset fill and not all of them. On a
fill by fill basis the performance degrades by a factor of 30 when
running the query in SQL Query Analyzer versus a fill from within the
app. Multiply the degradation in performance by 20 and you can see why
I am frustrated. The app takes forever (minutes) to load.
 
N

Nicholas Paldino [.NET/C# MVP]

Greg,

Can you provide a complete sample app (with a database file and code)
which would outline the problem. Without seeing any of the code, it's
impossible to tell at this point.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Greg said:
Based on your other posts, it seems like it's not so much the
individual
fills, but rather, the fact that you have so many of them. Are the
numbers
you gave for one individual call to Fill the data set, or is it for all
of
them?
Click to expand...
I believe you are on the right track when you say that it is the
interaction of the app with the database, but you haven't given many
details
about that. I don't think it is the SqlDataAdapter in itself. Have
you
tried just executing the command, getting a reader, and then cycling
through
it (columns and rows) to see what the performance there is? Or, maybe
just
filling one dataset, and measuring the performance there?
Click to expand...
news:[email protected]...
Click to expand...

On Aug 6, 7:36 pm, "Nicholas Paldino [.NET/C# MVP]"
Greg,
Click to expand...
It seems like something else is definitely going on here. I
can't
see
the code you are using, so I can't tell what is going on on the .NET
side.
However, from what you have shown of the execution tree, it seems
like
you
haven't optimized the ValidValues_HLAHighRes table for this query.
It's
doing an index scan, which is not really what you want. You should
probably
index it so that an index seek will be performed. If you have a
large
number of records in the ValidValues_HLAHighRes table, then that
could be
impacting performance.
Click to expand...
Of course, there is some overhead in populating a data set, but
from
what you are mentioning, it doesn't seem like it should be that
much.
Assuming a reasonable number of columns in the table, a few hundred
rows
really shouldn't take that long.
Click to expand...
I am trying to fill strongly typed datasets with data from a
SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts,
updates,
or deletes. When I call the Fill method to load the dataset, CPU
Usage
goes to 100%. These simple queries take forever to complete. We
are
only dealing with a few hundred rows. My windows form takes
minutes to
load because of the poor performance. The select statements
require 2
inner joins to aquire the required data columns. Similar fills
using
selects with a single inner join perforn OK. I tried running the
SQl
Profiler to analyze the problem. When I run the query from SQL
Query
Analyzer, SQL Profiler shows a duration of 30. When run from
withing
the C# .net application duration jumps to 9000 or 300 times
slower.
Reads stay the same. SQL execution plan is the same when using
Query
analyzer versus the application. It appears the SQL Data Provider
is
the source of the poor performance. Here is the SQL Select that
performs poorly:
Click to expand...
SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID =
L.HLALowResID
WHERE (C.CategoryName = 'A')
Click to expand...
Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]), SEEK:([L].[HLALowResID]=[H].
[HLALowResID]) ORDERED FORWARD)
Click to expand...
Here is the SQL Select that performs OK even though duration
doubles:
Click to expand...
SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName
Click to expand...
Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))
Click to expand...
Thes are quite similar excpet for an extra INNER JOIN. What can
the
souce of this poor perfomance be? Why does CPU get pegged at 100%
when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
The HLAHighRes table has 3600 rows, HLALowRes has 140 rows, And
HLACategories has just 6 rows.
The queries run very fast from query analyzer but get terribly slow
when called by the data adapter fill method.
CPU goes to 100%. In my DB experience it performs like it is doing
full table scans with thousands of rows.
All the tables are keyed by an identity column hence the ID in the
column name and have a primary clustered key index.
I tried adding an index on the combo of H.HLAHighResID,
H.HighResName,
H.HLALowResID
which was recommended by the profiler index analyzer. It really did
not effect performance.
Seems to me there is some issue with the interaction of the app and
DB.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

40 of the datasets get filled when the form loads. The 1st 10 that use
1 INNER JOIN load OK.
As soon as it hits the next 10 that have 2 INNER Joins performance
goes bad. Then the next 10 datasets have
the 1 INNER JOIN again and performance picks up. The last 10 datasets
filled are back to 2 INNER JOINS and performance goes bad again.
I have a Profiler Trace showing this. I tried changing the order by
putting the poor performers 1st. It did not matter.
The performance was still going from poor to good depending on the
query and not the order.- Hide quoted text -

- Show quoted text -
Click to expand...

The numbers I gave were for 1 dataset fill and not all of them. On a
fill by fill basis the performance degrades by a factor of 30 when
running the query in SQL Query Analyzer versus a fill from within the
app. Multiply the degradation in performance by 20 and you can see why
I am frustrated. The app takes forever (minutes) to load.
Click to expand...
 
G

Greg

Greg,

Can you provide a complete sample app (with a database file and code)
which would outline the problem. Without seeing any of the code, it's
impossible to tell at this point.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)




On Aug 7, 7:39 am, "Nicholas Paldino [.NET/C# MVP]"
Based on your other posts, it seems like it's not so much the
individual
fills, but rather, the fact that you have so many of them. Are the
numbers
you gave for one individual call to Fill the data set, or is it for all
of
them?
I believe you are on the right track when you say that it is the
interaction of the app with the database, but you haven't given many
details
about that. I don't think it is the SqlDataAdapter in itself. Have
you
tried just executing the command, getting a reader, and then cycling
through
it (columns and rows) to see what the performance there is? Or, maybe
just
filling one dataset, and measuring the performance there?
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

On Aug 6, 7:36 pm, "Nicholas Paldino [.NET/C# MVP]"
Greg,
It seems like something else is definitely going on here. I
can't
see
the code you are using, so I can't tell what is going on on the .NET
side.
However, from what you have shown of the execution tree, it seems
like
you
haven't optimized the ValidValues_HLAHighRes table for this query.
It's
doing an index scan, which is not really what you want. You should
probably
index it so that an index seek will be performed. If you have a
large
number of records in the ValidValues_HLAHighRes table, then that
could be
impacting performance.
Of course, there is some overhead in populating a data set, but
from
what you are mentioning, it doesn't seem like it should be that
much.
Assuming a reasonable number of columns in the table, a few hundred
rows
really shouldn't take that long.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I am trying to fill strongly typed datasets with data from a
SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts,
updates,
or deletes. When I call the Fill method to load the dataset, CPU
Usage
goes to 100%. These simple queries take forever to complete. We
are
only dealing with a few hundred rows. My windows form takes
minutes to
load because of the poor performance. The select statements
require 2
inner joins to aquire the required data columns. Similar fills
using
selects with a single inner join perforn OK. I tried running the
SQl
Profiler to analyze the problem. When I run the query from SQL
Query
Analyzer, SQL Profiler shows a duration of 30. When run from
withing
the C# .net application duration jumps to 9000 or 300 times
slower.
Reads stay the same. SQL execution plan is the same when using
Query
analyzer versus the application. It appears the SQL Data Provider
is
the source of the poor performance. Here is the SQL Select that
performs poorly:
SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID =
L.HLALowResID
WHERE (C.CategoryName = 'A')
Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]), SEEK:([L].[HLALowResID]=[H].
[HLALowResID]) ORDERED FORWARD)
Here is the SQL Select that performs OK even though duration
doubles:
SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName
Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))
Thes are quite similar excpet for an extra INNER JOIN. What can
the
souce of this poor perfomance be? Why does CPU get pegged at 100%
when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?- Hide quoted text -
- Show quoted text -
The HLAHighRes table has 3600 rows, HLALowRes has 140 rows, And
HLACategories has just 6 rows.
The queries run very fast from query analyzer but get terribly slow
when called by the data adapter fill method.
CPU goes to 100%. In my DB experience it performs like it is doing
full table scans with thousands of rows.
All the tables are keyed by an identity column hence the ID in the
column name and have a primary clustered key index.
I tried adding an index on the combo of H.HLAHighResID,
H.HighResName,
H.HLALowResID
which was recommended by the profiler index analyzer. It really did
not effect performance.
Seems to me there is some issue with the interaction of the app and
DB.- Hide quoted text -
- Show quoted text -
40 of the datasets get filled when the form loads. The 1st 10 that use
1 INNER JOIN load OK.
As soon as it hits the next 10 that have 2 INNER Joins performance
goes bad. Then the next 10 datasets have
the 1 INNER JOIN again and performance picks up. The last 10 datasets
filled are back to 2 INNER JOINS and performance goes bad again.
I have a Profiler Trace showing this. I tried changing the order by
putting the poor performers 1st. It did not matter.
The performance was still going from poor to good depending on the
query and not the order.- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
The numbers I gave were for 1 dataset fill and not all of them. On a
fill by fill basis the performance degrades by a factor of 30 when
running the query in SQL Query Analyzer versus a fill from within the
app. Multiply the degradation in performance by 20 and you can see why
I am frustrated. The app takes forever (minutes) to load.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I may be able to create a sample. The entire app and DB would be a lot
to deal with.
The app is also medical so the data is confidential and constrained by
HIPPA rules.
However a sample of just the 3 tables, 40 datasets, and 1 form might
be doable.
Apparently no one else had ever had such issues. I was hoping this was
something somebody had seen before.

I have run into other problems related to the use of so many datasets
in VS .Net.
Any time I made a small change to a dataset it would regenerate all
the datasets in the app.
This would be time consuming as well. That turned out to be a bug and
a hotfix was put out by MS.
I suspect there are some other underlying issues related to datasets
most likely due to the disconnected
DB approach MS has chosen to implement, I'm thinking each fill causes
a connection to be established, hand shaking to occur,
passing of data back and forth and then closing the connection. A
persistant DB connection in this scenario would
probably perform better but is not an option. I may need to look at
denorrnalizing the data to avoid the INNER JOINS in the query.
Anyway, thanks for all the input.
 
G

GS

what if you change the query in your SQL adaptor fill to execute a store
proc? that may not be a total solution but will be faster unless it will
result in substantially more network traffic in the output
Greg said:
Greg,

Can you provide a complete sample app (with a database file and code)
which would outline the problem. Without seeing any of the code, it's
impossible to tell at this point.

--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)




On Aug 7, 7:39 am, "Nicholas Paldino [.NET/C# MVP]"
Click to expand...
Based on your other posts, it seems like it's not so much the
individual
fills, but rather, the fact that you have so many of them. Are the
numbers
you gave for one individual call to Fill the data set, or is it for all
of
them?
Click to expand...
I believe you are on the right track when you say that it is the
interaction of the app with the database, but you haven't given many
details
about that. I don't think it is the SqlDataAdapter in itself. Have
you
tried just executing the command, getting a reader, and then cycling
through
it (columns and rows) to see what the performance there is? Or, maybe
just
filling one dataset, and measuring the performance there?
Click to expand...
news:[email protected]...
Click to expand...

On Aug 6, 7:36 pm, "Nicholas Paldino [.NET/C# MVP]"
Greg,
Click to expand...
It seems like something else is definitely going on here. I
can't
see
the code you are using, so I can't tell what is going on on the ..NET
side.
However, from what you have shown of the execution tree, it seems
like
you
haven't optimized the ValidValues_HLAHighRes table for this query.
It's
doing an index scan, which is not really what you want. You should
probably
index it so that an index seek will be performed. If you have a
large
number of records in the ValidValues_HLAHighRes table, then that
could be
impacting performance.
Click to expand...
Of course, there is some overhead in populating a data set, but
from
what you are mentioning, it doesn't seem like it should be that
much.
Assuming a reasonable number of columns in the table, a few hundred
rows
really shouldn't take that long.
Click to expand...
I am trying to fill strongly typed datasets with data from a
SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts,
updates,
or deletes. When I call the Fill method to load the dataset, CPU
Usage
goes to 100%. These simple queries take forever to complete. We
are
only dealing with a few hundred rows. My windows form takes
minutes to
load because of the poor performance. The select statements
require 2
inner joins to aquire the required data columns. Similar fills
using
selects with a single inner join perforn OK. I tried running the
SQl
Profiler to analyze the problem. When I run the query from SQL
Query
Analyzer, SQL Profiler shows a duration of 30. When run from
withing
the C# .net application duration jumps to 9000 or 300 times
slower.
Reads stay the same. SQL execution plan is the same when using
Query
analyzer versus the application. It appears the SQL Data Provider
is
the source of the poor performance. Here is the SQL Select that
performs poorly:
Click to expand...
SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID =
L.HLALowResID
WHERE (C.CategoryName = 'A')
Click to expand...
Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]), SEEK:([L].[HLALowResID]=[H].
[HLALowResID]) ORDERED FORWARD)
Click to expand...
Here is the SQL Select that performs OK even though duration
doubles:
Click to expand...
SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName
Click to expand...
Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))
Click to expand...
Thes are quite similar excpet for an extra INNER JOIN. What can
the
souce of this poor perfomance be? Why does CPU get pegged at 100%
when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
The HLAHighRes table has 3600 rows, HLALowRes has 140 rows, And
HLACategories has just 6 rows.
The queries run very fast from query analyzer but get terribly slow
when called by the data adapter fill method.
CPU goes to 100%. In my DB experience it performs like it is doing
full table scans with thousands of rows.
All the tables are keyed by an identity column hence the ID in the
column name and have a primary clustered key index.
I tried adding an index on the combo of H.HLAHighResID,
H.HighResName,
H.HLALowResID
which was recommended by the profiler index analyzer. It really did
not effect performance.
Seems to me there is some issue with the interaction of the app and
DB.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
40 of the datasets get filled when the form loads. The 1st 10 that use
1 INNER JOIN load OK.
As soon as it hits the next 10 that have 2 INNER Joins performance
goes bad. Then the next 10 datasets have
the 1 INNER JOIN again and performance picks up. The last 10 datasets
filled are back to 2 INNER JOINS and performance goes bad again.
I have a Profiler Trace showing this. I tried changing the order by
putting the poor performers 1st. It did not matter.
The performance was still going from poor to good depending on the
query and not the order.- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...
The numbers I gave were for 1 dataset fill and not all of them. On a
fill by fill basis the performance degrades by a factor of 30 when
running the query in SQL Query Analyzer versus a fill from within the
app. Multiply the degradation in performance by 20 and you can see why
I am frustrated. The app takes forever (minutes) to load.- Hide quoted
Click to expand...
text -

- Show quoted text -
Click to expand...

I may be able to create a sample. The entire app and DB would be a lot
to deal with.
The app is also medical so the data is confidential and constrained by
HIPPA rules.
However a sample of just the 3 tables, 40 datasets, and 1 form might
be doable.
Apparently no one else had ever had such issues. I was hoping this was
something somebody had seen before.

I have run into other problems related to the use of so many datasets
in VS .Net.
Any time I made a small change to a dataset it would regenerate all
the datasets in the app.
This would be time consuming as well. That turned out to be a bug and
a hotfix was put out by MS.
I suspect there are some other underlying issues related to datasets
most likely due to the disconnected
DB approach MS has chosen to implement, I'm thinking each fill causes
a connection to be established, hand shaking to occur,
passing of data back and forth and then closing the connection. A
persistant DB connection in this scenario would
probably perform better but is not an option. I may need to look at
denorrnalizing the data to avoid the INNER JOINS in the query.
Anyway, thanks for all the input.
Click to expand...
 
G

Greg

what if you change the query in your SQL adaptorfill to execute a store
proc? that may not be a total solution but will be faster unless it will



Greg,
Can you provide a complete sample app (with a database file and code)
which would outline the problem. Without seeing any of the code, it's
impossible to tell at this point.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

On Aug 7, 7:39 am, "Nicholas Paldino [.NET/C# MVP]"
Based on your other posts, it seems like it's not so much the
individual
fills, but rather, the fact that you have so many of them. Are the
numbers
you gave for one individual call toFillthe data set, or is it for all
of
them?
I believe you are on the right track when you say that it is the
interaction of the app with the database, but you haven't given many
details
about that. I don't think it is the SqlDataAdapter in itself. Have
you
tried just executing the command, getting a reader, and then cycling
through
it (columns and rows) to see what theperformancethere is? Or, maybe
just
filling onedataset, and measuring theperformancethere?
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

On Aug 6, 7:36 pm, "Nicholas Paldino [.NET/C# MVP]"
Greg,
It seems like something else is definitely going on here.I
can't
see
the code you are using, so I can't tell what is going on on the .NET
side.
However, from what you have shown of the execution tree, it seems
like
you
haven't optimized the ValidValues_HLAHighRes table for this query.
It's
doing an index scan, which is not really what you want. You should
probably
index it so that an index seek will be performed. If you have a
large
number of records in the ValidValues_HLAHighRes table, then that
could be
impactingperformance.
Of course, there is some overhead in populating a data set, but
from
what you are mentioning, it doesn't seem like it should be that
much.
Assuming a reasonable number of columns in the table, a few hundred
rows
really shouldn't take that long.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I am trying tofillstrongly typed datasets with data from a
SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts,
updates,
or deletes. When I call theFillmethod to load thedataset, CPU
Usage
goes to 100%. These simple queries take forever to complete. We
are
only dealing with a few hundred rows. My windows form takes
minutes to
load because of thepoorperformance. The select statements
require 2
inner joins to aquire the required data columns. Similar fills
using
selects with a single inner join perforn OK. I tried running the
SQl
Profiler to analyze the problem. When I run the query from SQL
Query
Analyzer, SQL Profiler shows a duration of 30. When run from
withing
the C# .net application duration jumps to 9000 or 300 times
slower.
Reads stay the same. SQL execution plan is the same when using
Query
analyzer versus the application. It appears the SQL Data Provider
is
the source of thepoorperformance. Here is the SQL Select that
performs poorly:
SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID =
L.HLALowResID
WHERE (C.CategoryName = 'A')
Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]),
Click to expand...
SEEK:([L].[HLALowResID]=[H].


[HLALowResID]) ORDERED FORWARD)
Here is the SQL Select that performs OK even though duration
doubles:
SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName
Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))
Thes are quite similar excpet for an extra INNER JOIN. What can
the
souce of thispoorperfomance be? Why does CPU get pegged at 100%
when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?- Hide quoted text -
- Show quoted text -
The HLAHighRes table has 3600 rows, HLALowRes has 140 rows, And
HLACategories has just 6 rows.
The queries run very fast from query analyzer but get terribly slow
when called by the data adapterfillmethod.
CPU goes to 100%. In my DB experience it performs like it is doing
full table scans with thousands of rows.
All the tables are keyed by an identity column hence the ID in the
column name and have a primary clustered key index.
I tried adding an index on the combo of H.HLAHighResID,
H.HighResName,
H.HLALowResID
which was recommended by the profiler index analyzer. It really did
not effectperformance.
Seems to me there is some issue with the interaction of the app and
DB.- Hide quoted text -
- Show quoted text -
40 of the datasets get filled when the form loads. The 1st 10 that use
1 INNER JOIN load OK.
As soon as it hits the next 10 that have 2 INNER Joinsperformance
goes bad. Then the next 10 datasets have
the 1 INNER JOIN again andperformancepicks up. The last 10 datasets
filled are back to 2 INNER JOINS andperformancegoes bad again.
I have a Profiler Trace showing this. I tried changing the order by
putting thepoorperformers 1st. It did not matter.
Theperformancewas still going frompoorto good depending on the
query and not the order.- Hide quoted text -
- Show quoted text -
The numbers I gave were for 1datasetfilland not all of them. On a
fillbyfillbasis theperformancedegrades by a factor of 30 when
running the query in SQL Query Analyzer versus afillfrom within the
app. Multiply the degradation inperformanceby 20 and you can see why
I am frustrated. The app takes forever (minutes) to load.- Hide quoted
Click to expand...
Click to expand...
text -
I may be able to create a sample. The entire app and DB would be a lot
to deal with.
The app is also medical so the data is confidential and constrained by
HIPPA rules.
However a sample of just the 3 tables, 40 datasets, and 1 form might
be doable.
Apparently no one else had ever had such issues. I was
Click to expand...

...

read more »- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...

Since I posted this I have tried several other approaches. I switched
to a stored procedure. This did not effect performance. So next I
tried a DataReader approach. The data reader returned the data from
SQL Server very quickly. So it looked promising. However, when I took
the data stream from the reader and tried to fill a dataset the
performance went to hell again. So I can say with some confidence that
the poor performance is related to the datase fill method. I have seen
some other posts that seem to implicate the XML Reader as the culprit.
Either way it seems to be an internals issue that I cannot effect. I
am now trying to change the app to use strictly data readers. The data
ends up in a combo box. So filling the combo box from a datareader
should be easy enough. The problem is that the list being displayed in
the combo box is actually a subset of the returned rows. The list is
filterd using a dataview based on a filter value selected from another
combo box. This approach only works when all the combo boxes involved
are linked to a dataset as the datasource for the combo boxes. If I
dissasociate the combo box from the dataset as datasorce, I cannot use
a dataview to filter the displayed combo box list. So now I am left
calling the datareader over and over again whenever the value is
changed in the combo box used to set the filter in the dataview.
 
G

Greg

what if you change the query in your SQL adaptor fill to execute a store
proc? that may not be a total solution but will be faster unless it will



Greg,
Can you provide a complete sample app (with a database file and code)
which would outline the problem. Without seeing any of the code, it's
impossible to tell at this point.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

On Aug 7, 7:39 am, "Nicholas Paldino [.NET/C# MVP]"
Based on your other posts, it seems like it's not so much the
individual
fills, but rather, the fact that you have so many of them. Are the
numbers
you gave for one individual call to Fill the data set, or is it for all
of
them?
I believe you are on the right track when you say that it is the
interaction of the app with the database, but you haven't given many
details
about that. I don't think it is the SqlDataAdapter in itself. Have
you
tried just executing the command, getting a reader, and then cycling
through
it (columns and rows) to see what theperformancethere is? Or, maybe
just
filling one dataset, and measuring theperformancethere?
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

On Aug 6, 7:36 pm, "Nicholas Paldino [.NET/C# MVP]"
Greg,
It seems like something else is definitely going on here.I
can't
see
the code you are using, so I can't tell what is going on on the .NET
side.
However, from what you have shown of the execution tree, it seems
like
you
haven't optimized the ValidValues_HLAHighRes table for this query.
It's
doing an index scan, which is not really what you want. You should
probably
index it so that an index seek will be performed. If you have a
large
number of records in the ValidValues_HLAHighRes table, then that
could be
impactingperformance.
Of course, there is some overhead in populating a data set, but
from
what you are mentioning, it doesn't seem like it should be that
much.
Assuming a reasonable number of columns in the table, a few hundred
rows
really shouldn't take that long.
--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

I am trying to fill strongly typed datasets with data from a
SQLServer
DB. The data is used as a datsource for drop down lists. The data
adapters are configured to perform only selects, no inserts,
updates,
or deletes. When I call the Fill method to load the dataset, CPU
Usage
goes to 100%. These simple queries take forever to complete. We
are
only dealing with a few hundred rows. My windows form takes
minutes to
load because of thepoorperformance. The select statements
require 2
inner joins to aquire the required data columns. Similar fills
using
selects with a single inner join perforn OK. I tried running the
SQl
Profiler to analyze the problem. When I run the query from SQL
Query
Analyzer, SQL Profiler shows a duration of 30. When run from
withing
the C# .net application duration jumps to 9000 or 300 times
slower.
Reads stay the same. SQL execution plan is the same when using
Query
analyzer versus the application. It appears the SQL Data Provider
is
the source of thepoorperformance. Here is the SQL Select that
performs poorly:
SELECT H.HLAHighResID, H.HighResName, H.HLALowResID, L.LowResName
FROM ValidValues_HLAHighRes H
INNER JOIN ValidValues_HLACategories C ON H.HLACategoryID =
C.HLACategoryID
INNER JOIN ValidValues_HLALowRes L ON H.HLALowResID =
L.HLALowResID
WHERE (C.CategoryName = 'A')
Execution Tree
--------------
Nested Loops(Inner Join, OUTER REFERENCES:([H].[HLALowResID]))
|--Nested Loops(Inner Join, WHERE:([H].[HLACategoryID]=[C].
[HLACategoryID]))
| |--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLAHighRes].[PK_ValidValues_HLAHighRes] AS [H]))
|--Clustered Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLALowRes].
[PK_ValidValues_HLALowRes] AS [L]),
Click to expand...
SEEK:([L].[HLALowResID]=[H].


[HLALowResID]) ORDERED FORWARD)
Here is the SQL Select that performs OK even though duration
doubles:
SELECT L.HLALowResID, L.LowResName
FROM ValidValues_HLALowRes L
INNER JOIN ValidValues_HLACategories C ON L.HLACategoryID =
C.HLACategoryID
WHERE (C.CategoryName = 'A') ORDER BY L.LowResName
Execution Tree
--------------
Sort(ORDER BY:([L].[LowResName] ASC))
|--Nested Loops(Inner Join, WHERE:([C].[HLACategoryID]=[L].
[HLACategoryID]))
|--Index
Seek(OBJECT:([test].[dbo].[ValidValues_HLACategories].
[IX_ValidValues_HLACategories_1] AS [C]), SEEK:([C].
[CategoryName]='A') ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([test].[dbo].
[ValidValues_HLALowRes].[PK_ValidValues_HLALowRes] AS [L]))
Thes are quite similar excpet for an extra INNER JOIN. What can
the
souce of thispoorperfomance be? Why does CPU get pegged at 100%
when
I run these selects from the application? I thought trhe SQL Data
Provider was the preferred choice when accessing a SQL Server DB.
Should I try an OLE Data Adapter?- Hide quoted text -
- Show quoted text -
The HLAHighRes table has 3600 rows, HLALowRes has 140 rows, And
HLACategories has just 6 rows.
The queries run very fast from query analyzer but get terribly slow
when called by the data adapter fill method.
CPU goes to 100%. In my DB experience it performs like it is doing
full table scans with thousands of rows.
All the tables are keyed by an identity column hence the ID in the
column name and have a primary clustered key index.
I tried adding an index on the combo of H.HLAHighResID,
H.HighResName,
H.HLALowResID
which was recommended by the profiler index analyzer. It really did
not effectperformance.
Seems to me there is some issue with the interaction of the app and
DB.- Hide quoted text -
- Show quoted text -
40 of the datasets get filled when the form loads. The 1st 10 that use
1 INNER JOIN load OK.
As soon as it hits the next 10 that have 2 INNER Joinsperformance
goes bad. Then the next 10 datasets have
the 1 INNER JOIN again andperformancepicks up. The last 10 datasets
filled are back to 2 INNER JOINS andperformancegoes bad again.
I have a Profiler Trace showing this. I tried changing the order by
putting thepoorperformers 1st. It did not matter.
Theperformancewas still going frompoorto good depending on the
query and not the order.- Hide quoted text -
- Show quoted text -
The numbers I gave were for 1 dataset fill and not all of them. On a
fill by fill basis theperformancedegrades by a factor of 30 when
running the query in SQL Query Analyzer versus a fill from within the
app. Multiply the degradation inperformanceby 20 and you can see why
I am frustrated. The app takes forever (minutes) to load.- Hide quoted
Click to expand...
Click to expand...
text -
I may be able to create a sample. The entire app and DB would be a lot
to deal with.
The app is also medical so the data is confidential and constrained by
HIPPA rules.
However a sample of just the 3 tables, 40 datasets, and 1 form might
be doable.
Apparently no one else had ever had such issues. I was
Click to expand...

...

read more »- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -
Click to expand...

I beleive I have found the source of the poor performance. It is not
really the interaction with SQLServer that is slowing the app down. It
is the fact that the dataset or datatable in queston is bound to a
combo box. If the data is bound to the combo box before the dataset or
data table is filled performance goes to hell. If I fill the dataset
or datatable first and then bind it to the combo box, perofrmance
improves dramatically. If you code using the designer and set control
properties with the designer, the default code generated binds all the
controls in the initialize component method. So by default the
controls are bound before any data is populated into the datasources.
This is a case where the designer is perhaps not following "best
practices" when generating its code.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Download the JAVA , .NET and SQL Server interview with answers 2
FWT Newsletter - Weekly - October 25, 2004 8

Top