|
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.
|
|
|
|
|
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
modified 17-Dec-11 7:52am.
|
|
|
|
|
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 ON
DECLARE
@sqlStatement nvarchar(max),
@upperBound int
IF @startIndex < 1 SET @startIndex = 1
IF @pageSize < 1 SET @pageSize = 1
SET @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.
|
|
|
|
|
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
|
|
|
|
|
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? 
|
|
|
|
|
PIEBALDconsult wrote: Maybe theirs are better?
Trust me, they're not
|
|
|
|
|
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 
|
|
|
|
|
All the best,
Dan
|
|
|
|
|
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
|
|
|
|
|
Hi Bill,
There was no fault with the DevExpress grid (which truly is a stunning control and suite). It appears to have been some weird issue with the SQL Server Stored Proc Execution plan. 
|
|
|
|
|
Hi eveybody
I want to convert given string to ıf statment
For Ex
string condition="(a>45 && b<34) || (c>14 && d>56)";
if(condition)
{
//to do smth
}
I try to write my own parser but ıt will be very diffucult because There are lots of conditions can be wrıtten lıke
string cond="a<45&&(b>=56 || c<45)";
strinf con="((a==50 & b>45)||c>56) && e<457";
so How can ı do this ?
Its to important for me please help me ...
|
|
|
|
|
Surely a parser has no trouble with that.
Anyway, since you're only parsing an expression, you could use something slightly simpler than a parser - the Shunting Yard algorithm.
|
|
|
|
|
Looks like lots of C# implementations of this algorithm are out there:[^]
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
|
|
|
|
|
That's no easy task.
Here's an article that deals with many if not all of your problems.
It should get you started.
All the best,
Dan
|
|
|
|
|
cacampa wrote: I try to write my own parser but ıt will be very diffucult because There are lots of conditions can be wrıtten
That will always be true for this type of problem.
cacampa wrote: so How can ı do this ?
Either write your own parser or use one of the free tools around. Try a Google search for "parser", "yacc" and "antlr", and you should find lots of useful resources.
cacampa wrote: Its to important for me please help me
Other than suggesting tools and search options there is not much else that we can do for you.
Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman
|
|
|
|
|
Another solution ... around for a long time ... assuming your working in C# ... is to reference and invoke the JScript library and its 'eval' facility: [^].
Note: I have not used this myself, and it might be "tricky."
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
|
|
|
|
|
|
Manfred, please fix the broken url.
|
|
|
|
|
Thanks for pointing that out! I'm quite disturbed by the troubles I'm having with inserting links via my tablet device.
Cheers!
"With sufficient thrust, pigs fly just fine."
Ross Callon, The Twelve Networking Truths, RFC1925
|
|
|
|
|
Hi,
through Dynamic in C# 4.0 you can achieve this, but you need to investigate it
here is the link i found to execute string in c#
hope this will resolve your problem
thanks
-amit
|
|
|
|
|
Hello ppl....
please i need a help.. i have a c# subject at my university,they give me a bonus marks to make a program (project).. i make some research in the net .. but it was difficult to choose a program to work in it.. and i want some ideas for a programs ..
tkx
|
|
|
|
|
Sounds like the perfect opportunity to make Skynet.
Somebody in an online forum wrote: INTJs never really joke. They make a point. The joke is just a gift wrapper.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
