Re: SQl Data Provider Performance Issues

On Aug 15, 10:08 am, "GS" <gsmsnews.microsoft.co...@xxxxxxxxxxxxxxxxx>
wrote:
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
result in substantially more network traffic in the output"Greg" <gmicha....@xxxxxxxxxxxxx> wrote in message

news:1186536726.053619.15980@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx



On Aug 7, 10:55 am, "Nicholas Paldino [.NET/C# MVP]"
<m...@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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]
- m...@xxxxxxxxxxxxxxxxxxxxxxxxxxx

"Greg" <gmicha...@xxxxxxxxxxxxx> wrote in message

news:1186499523.874600.117200@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Aug 7, 9:05 am, Greg <gmicha...@xxxxxxxxxxxxx> wrote:
On Aug 7, 7:39 am, "Nicholas Paldino [.NET/C# MVP]"

<m...@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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]
- m...@xxxxxxxxxxxxxxxxxxxxxxxxxxx

"Greg" <gmicha...@xxxxxxxxxxxxx> wrote in message

news:1186468407.360935.9580@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Aug 6, 7:36 pm, "Nicholas Paldino [.NET/C# MVP]"
<m...@xxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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]
- m...@xxxxxxxxxxxxxxxxxxxxxxxxxxx

"Greg" <gmicha...@xxxxxxxxxxxxx> wrote in message

news:1186446086.255319.90070@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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]),

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
text -

- Show quoted 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

...

read more »- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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.

.

Relevant Pages

  • Re: Ranking query
    ... I expect that using a named query (as opposed to its SQL) will be OK. ... INNER JOIN qryRepairs AS I2 ...
    (microsoft.public.access.queries)
  • Re: SQl Data Provider Performance Issues
    ... Can you provide a complete sample app ... selects with a single inner join perforn OK. ... When I run the query from SQL ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQl Data Provider Performance Issues
    ... > selects with a single inner join perforn OK. ... When I run the query from SQL Query ... > Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQl Data Provider Performance Issues
    ... selects with a single inner join perforn OK. ... When I run the query from SQL Query ... Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SQl Data Provider Performance Issues
    ... selects with a single inner join perforn OK. ... When I run the query from SQL ... Analyzer, SQL Profiler shows a duration of 30. ...
    (microsoft.public.dotnet.languages.csharp)