|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
filling a dataset taking toooo much timeI 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 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 Kind of database: Sql Server
Kind of connection: SqlConnection (System.Data.SqlClient) Trying on my own development machine. ie, localhost Thanks Cor Ligthert wrote: Show quoteHide quote > 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 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 Hi Michelle,
To add to Cor's questions: how many rows are returned? how many columns? Bernie Yaeger <mich_st***@yahoo.com> wrote in message Show quoteHide quote news:1109501505.640745.113440@f14g2000cwb.googlegroups.com... > 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 > 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. <mich_st***@yahoo.com> wrote in message Show quoteHide quote news:1109501505.640745.113440@f14g2000cwb.googlegroups.com... > 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 > Hi Alex,
That, plus creation of the .xml file. That's why I want to know the row/col details. Bernie Show quoteHide quote "Alex Homer" <a***@stonebroom.com> wrote in message news:%23SaA9WOHFHA.576@TK2MSFTNGP15.phx.gbl... > 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. > > <mich_st***@yahoo.com> wrote in message > news:1109501505.640745.113440@f14g2000cwb.googlegroups.com... >> 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 >> > > 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. -- Show quoteHide quote____________________________________ 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. __________________________________ "Alex Homer" <a***@stonebroom.com> wrote in message news:%23SaA9WOHFHA.576@TK2MSFTNGP15.phx.gbl... > 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. > > <mich_st***@yahoo.com> wrote in message > news:1109501505.640745.113440@f14g2000cwb.googlegroups.com... >> 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 >> > > 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... 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. -- Show quoteHide quote____________________________________ 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_st***@yahoo.com> wrote in message news:1109656795.257929.233290@l41g2000cwc.googlegroups.com... > 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... >  |
|||||||||||||||||||||||
Other interesting topics