Performance issue with TableAdapter?
-
Why on earth return such a huge number of rows? Use some sort of paging please, pretty please. Anyway, if for some naughty reason you must get all the data at once, using a DataReader to populate a list of someCalss should be faster. [add] Consider this scenario: What would happen if codeproject would return all the post in one forum at once? * the servers could in the best case scenario return a timeout for most of the users * the page will eat huge amount of memory on the client machine web browser and so on... [/add]
All the best, Dan
Hi Dan, Thanks for the info. I should have mentioned this is a WinForms app, not a Web one. I need all the data in the front end as the user is able to do on the fly grouping and drill down, custom sorting and filtering, put group totals on etc, so if I were to page data back as needed, none of this would work and these are the features which separate my apps from others...
-
Hi Dan, Thanks for the info. I should have mentioned this is a WinForms app, not a Web one. I need all the data in the front end as the user is able to do on the fly grouping and drill down, custom sorting and filtering, put group totals on etc, so if I were to page data back as needed, none of this would work and these are the features which separate my apps from others...
There is nothing stopping you to do that directly in sql while still using paging. Here's a simple example of SQL:
Create PROCEDURE spGetAllEmployee
(
@startIndex int,
@pageSize int,
@sortBy nvarchar(30),
@totalEmployees int OUTPUT
)
AS
SET NOCOUNT ONDECLARE
@sqlStatement nvarchar(max),
@upperBound intIF @startIndex < 1 SET @startIndex = 1
IF @pageSize < 1 SET @pageSize = 1SET @upperBound = @startIndex + @pageSize
Select @totalEmployees=Count(*) From Employee
SET @sqlStatement = ' SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, *
FROM Employee
) AS E
WHERE rowNumber >= ' + CONVERT(varchar(9), @startIndex) + ' AND
rowNumber < ' + CONVERT(varchar(9), @upperBound)
exec (@sqlStatement)Taken from this article. Sure it's for ASP but you can adapt. The idea was that you create dynamic grouping/sorting/filtering... while still using paging. Else, try loading the data using a data reader, as I said it should be faster. No guaranties though. :)
All the best, Dan
-
There is nothing stopping you to do that directly in sql while still using paging. Here's a simple example of SQL:
Create PROCEDURE spGetAllEmployee
(
@startIndex int,
@pageSize int,
@sortBy nvarchar(30),
@totalEmployees int OUTPUT
)
AS
SET NOCOUNT ONDECLARE
@sqlStatement nvarchar(max),
@upperBound intIF @startIndex < 1 SET @startIndex = 1
IF @pageSize < 1 SET @pageSize = 1SET @upperBound = @startIndex + @pageSize
Select @totalEmployees=Count(*) From Employee
SET @sqlStatement = ' SELECT E.EmployeeID, E.EmployeeCode, E.Name, E.Department, E.Salary
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @sortBy + ') AS rowNumber, *
FROM Employee
) AS E
WHERE rowNumber >= ' + CONVERT(varchar(9), @startIndex) + ' AND
rowNumber < ' + CONVERT(varchar(9), @upperBound)
exec (@sqlStatement)Taken from this article. Sure it's for ASP but you can adapt. The idea was that you create dynamic grouping/sorting/filtering... while still using paging. Else, try loading the data using a data reader, as I said it should be faster. No guaranties though. :)
All the best, Dan
Thanks again Dan for the response. The problem is that I don't use SQL for the grouping as it's all done through the front-end on a Developer Express data grid I use, and it needs all the data loaded in to allow the dynamic grouping to work, same goes for the filters etc. I'll look into the DataReader though to see if that improves things.
-
Hi Dan, Thanks for the info. I should have mentioned this is a WinForms app, not a Web one. I need all the data in the front end as the user is able to do on the fly grouping and drill down, custom sorting and filtering, put group totals on etc, so if I were to page data back as needed, none of this would work and these are the features which separate my apps from others...
I never use DataAdapters/TableAdapters or DataSets; they're more trouble than they're worth.
MarkB123 wrote:
which separate my apps from others...
Maybe theirs are better? :-D
-
Thanks again Dan for the response. The problem is that I don't use SQL for the grouping as it's all done through the front-end on a Developer Express data grid I use, and it needs all the data loaded in to allow the dynamic grouping to work, same goes for the filters etc. I'll look into the DataReader though to see if that improves things.
Yeah, I understood that you're loading all data, then filtering/sorting/grouping... on client. I only pledged against that. The data reader should help a bit. But what happens when you'll have 300.000+ rows of data? Sooner or later your technique will bite you(not gonna say where, to keep it KSS) ;)
All the best, Dan
-
Hi, I have a perceived performance issue doing a Fill on a Table Adapter... The Sql Server Stored Procedure when executed on the SQL Server (which I have running on my development PC) takes around 3 seconds to complete returning approximately 6500 rows and 98 columns wide. When I run the same Stored Procedure with same parameters on my front-end c# app, and I do a TableAdapter.Fill(... it is taking around 36 seconds to populate the table adapter with the results of the Stored Procedure. When it is rolled out to the live server, it can take over a minute resulting in a query timeout. I realise this is a difficult thing to quantify, but does this sound excessive? Is there anything I can do to speed this up (obviously I could return less columns or rows but this is not really an option for me). Often when I hook up the table adapter in a dataset in VS, it puts in column lengths for computed columns as quite small, eg string 12. I usually increase these to string 50 just in case I decide to adjust the SP with longer values for the computed columns in the future (so I don't need to rebuild the front end). Will this cause a hit on the front-end when populating? Many thanks for taking the time to read this.
I have made a break through - If I remove the date filter from my where clause on the Stored Proc, the data loads in a under a second (down from 36+ seconds). The where clause has the following in it... WHERE (@FromDate IS NULL OR ISNULL(A.[CallOutDateTime], '2900-12-31') >= @FromDate) AND ((@ToDate IS NULL OR @ToDate = '1900-01-01') OR ISNULL(A.[CallOutDateTime], '1900-01-01') <= @ToDate) Any ideas on why this would be causing the problem, bearing in mind when I execute the stored proc in SQL Server and pass it the same parameters as my front-end passes it runs in a second also, regardless of if I have the dates in the where cluase)? Bearing this in mind, I know it's not an indexing issue.
-
I have made a break through - If I remove the date filter from my where clause on the Stored Proc, the data loads in a under a second (down from 36+ seconds). The where clause has the following in it... WHERE (@FromDate IS NULL OR ISNULL(A.[CallOutDateTime], '2900-12-31') >= @FromDate) AND ((@ToDate IS NULL OR @ToDate = '1900-01-01') OR ISNULL(A.[CallOutDateTime], '1900-01-01') <= @ToDate) Any ideas on why this would be causing the problem, bearing in mind when I execute the stored proc in SQL Server and pass it the same parameters as my front-end passes it runs in a second also, regardless of if I have the dates in the where cluase)? Bearing this in mind, I know it's not an indexing issue.
Just put my Dates back into my where clause and it will now load the full record set in a second. Problem solved - Weird! I can only assume that changing the Where and removing the dates forced SQL Server to change the execution plan and putting the dates back used a new execution plan making more efficient use of the indexes. Anyway, speed issue now gone - users happy :)
-
I never use DataAdapters/TableAdapters or DataSets; they're more trouble than they're worth.
MarkB123 wrote:
which separate my apps from others...
Maybe theirs are better? :-D
-
Just put my Dates back into my where clause and it will now load the full record set in a second. Problem solved - Weird! I can only assume that changing the Where and removing the dates forced SQL Server to change the execution plan and putting the dates back used a new execution plan making more efficient use of the indexes. Anyway, speed issue now gone - users happy :)
-
Just put my Dates back into my where clause and it will now load the full record set in a second. Problem solved - Weird! I can only assume that changing the Where and removing the dates forced SQL Server to change the execution plan and putting the dates back used a new execution plan making more efficient use of the indexes. Anyway, speed issue now gone - users happy :)
Mark, glad you worked it out; if you feel charitable, you might post what happened here on on the DevXpress forum for their GridView, since it might help others using that Grid ? Assuming the performance issue had something to do with DevX's Grid, specifically. best, Bill
When I consider the brief span of my life, swallowed up in the eternity before and after, the little space which I fill, and even can see, engulfed in the infinite immensity of spaces of which I am ignorant, and which knows me not, I am frightened, and am astonished at being here rather than there; for there is no reason why here rather than there, now rather than then.
Blaise Pascal
-
Mark, glad you worked it out; if you feel charitable, you might post what happened here on on the DevXpress forum for their GridView, since it might help others using that Grid ? Assuming the performance issue had something to do with DevX's Grid, specifically. best, Bill
When I consider the brief span of my life, swallowed up in the eternity before and after, the little space which I fill, and even can see, engulfed in the infinite immensity of spaces of which I am ignorant, and which knows me not, I am frightened, and am astonished at being here rather than there; for there is no reason why here rather than there, now rather than then.
Blaise Pascal