filling a dataset taking toooo much time

[mich_stone]

Hi world.

I have a dataadapter whose select statements does some joins between
some tables and a view.

Running this select statement in Query Analyzer gives me my result in
under 2 seconds (i can digest it even if it takes 4 seconds which is
twice this time).

But when i do myAdapter.Fill (myTypedDataSet); it takes about 15
seconds to complete!!!

Why? If my query is so bad then won't it take as much time in query
analyzer?

To make sure it is a filling problem, I clicked on my DataAdapter (in
design mode), and in the properties window, clicked PREVIEW DATA, chose
my dataset, "FILL" and still the same thing.

To make diagnosis simpler, I have tried the following and all gave the
same slow results
1. Converting typed dataset to untyped
2. Removing key from dataset and putting back etc
3. (i read somewhere that people disagree when they talk about
datareaders vs. datasets) Using datareader instead of dataset as a
datasource.
4. did a while (reader.Read()); just to see how much time my debugger
will remain on that line, and believe me, it took the same 15 seconds.

Thanks a lot...

Regards,

Michelle

 

[Cor Ligthert]

Michelle

And some more information by instance

What kind of database?
What kind of connection SQLclient, OracleClient, OleDB, ODBC?
Database on Lan, Internet, direct?

Cor

 

[mich_stone]

Kind of database: Sql Server
Kind of connection: SqlConnection (System.Data.SqlClient)
Trying on my own development machine. ie, localhost

Thanks

 

[Cor Ligthert]

Michelle,

What you did not try yet was reading the tables seperated (with primary
keys) and creating the relation afterwards. Is that an idea to try?

(Which makes it direct easier to update)

Cor

 

[Bernie Yaeger]

Hi Michelle,

To add to Cor's questions:

how many rows are returned?
how many columns?

Bernie Yaeger

 

[Alex Homer]

If it takes 2 seconds in Query Ananlyser, it sounds like there are lots of
rows. Performance of the DataSet when loading multiple thousands of rows is
know to be slow due to internal indexing, etc. In V2 it is much quicker.

 

[Bernie Yaeger]

Hi Alex,

That, plus creation of the .xml file. That's why I want to know the row/col
details.

Bernie

 

[William \(Bill\) Vaughn]

And as we've said a thousand times before, ADO.NET was never designed as a
bulk copy interface--it's a query interface designed to return a few rows
based on a focused query. Fetch just the rows you need and no more. If you
have to do something on all of the rows in a table/product, do it on the
server in a SP.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[mich_stone]

Okay one more info I discovered ....

The actual query involves a JOIN with a view. I tried avoiding the join
with the view and the filling of the dataset happened in just TWO
SECONDS... But if i join with a view it takes a looong time.

I will post the select statement. For the time being i am filling two
datasets and through code matching the two, as Cor suggested (thanks
Cor). But it is still kinda slow. Nothing like joining the view and the
tables (as query analyzer did it very very fast)

This is the VIEW
CREATE VIEW dbo.VACCBAL WITH SCHEMABINDING
AS
SELECT dbo.TGeneralLedger.IDChartOfAccounts,
SUM(isnull(dbo.TGeneralLedger.fDebit,0)) AS SumDebit,
SUM(isnull(dbo.TGeneralLedger.fCredit,0)) AS SumCredit, COUNT_BIG(*) AS
COUNT
FROM dbo.TGeneralLedger INNER JOIN
dbo.TVoucher ON dbo.TGeneralLedger.IDVoucher =
dbo.TVoucher.IDVoucher
WHERE (dbo.TVoucher.bPosted = 1) AND (dbo.TVoucher.bDeleted = 0)
GROUP BY dbo.TGeneralLedger.IDChartOfAccounts

Rows returned is approximately 50 to 60. The TGeneralLedger table
contains about 100,000 records. But this view gives me my result in
about a second or less, which is fine with me.

Now here is the statement joining with the JOIN

SELECT TChartOfAccounts.IDChartOfAccounts, VACCBAL.SUMDEBIT AS AccBal,
CONVERT(varchar, RTRIM(TChartOfAccounts.sAccountNumber)) AS
sAccountNumber, CONVERT(varchar, RTRIM(TChartOfAccounts.sNameEng)) AS
sNameEng, CONVERT(varchar, RTRIM(TChartOfAccounts.sType)) AS sType,
CONVERT(varchar, RTRIM(TChartOfAccounts.sGroup)) AS sGroup,
CONVERT(Varchar, RTRIM(TChartOfAccounts.sControl)) AS sControl,
CONVERT(Varchar, RTRIM(TChartOfAccounts.sSubLedgerType)) AS
sSubLedgerType, TCOAControl.sName AS sDescControl,
TCOASubLedgerType.sName AS sDescSubLedgerType, TCOATypeAccount.sName AS
sDescType FROM TChartOfAccounts INNER JOIN TCOAControl ON
TChartOfAccounts.sControl = TCOAControl.sCode INNER JOIN
TCOASubLedgerType ON TChartOfAccounts.sSubLedgerType =
TCOASubLedgerType.sCode INNER JOIN TCOATypeAccount ON
TChartOfAccounts.sType = TCOATypeAccount.sCode LEFT OUTER JOIN VACCBAL
ON TChartOfAccounts.IDChartOfAccounts = VACCBAL.IDChartOfAccounts WHERE
(CONVERT(varchar, RTRIM(TChartOfAccounts.sAccountNumber)) LIKE
@sAccountNumber) AND (CONVERT(varchar,
RTRIM(TChartOfAccounts.sNameEng)) LIKE @sNameEng) AND (CONVERT(varchar,
RTRIM(TChartOfAccounts.sType)) LIKE @sType) AND (CONVERT(varchar,
RTRIM(TChartOfAccounts.sGroup)) LIKE @sGroup) AND (CONVERT(Varchar,
RTRIM(TChartOfAccounts.sControl)) LIKE @sControl) AND (CONVERT(Varchar,
RTRIM(TChartOfAccounts.sSubLedgerType)) LIKE @sSubLedgerType)

The second column being selected (with alias ACCBAL) is from the VIEW.
But if i remove VACC.SumDEBIT AS ACCBAL and put 0 AS ACCBAL instead, it
is absolutely fast.

I googled around for a day or two and i realized that things will speed
up if i put indexes.

I hope this info is sufficient for someone to help me out.

Thanks...

 

[William \(Bill\) Vaughn]

Again, you can't measure overall query performance with QA.
However, you can use QA to see if the query is efficiently using the
indexes.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________