Design Issue

  • Thread starter Thread starter Jose
  • Start date Start date
J

Jose

Hi All,

(I realise this isn't a specific dotnet question but I'm using dotnet
to implement it.)

I have a table of data that could run to about 50,000 rows. A user will
enter a some data in a web form and I need to return a paged set of
results depending on the value of the data. However I cannot process
this value in SQL I need to perform some complex calculations that can
only be done in code. My question is this, is there a better way than
returning all the rows (just the values I need not the entire row) run
the processing query and then display 10 records? It just seems a bad
way to get 10 records!!!

Thanks in advance for any suggestions.

Jose
 
Hello jose,

j> I have a table of data that could run to about 50,000 rows. A user
j> will enter a some data in a web form and I need to return a paged set
j> of results depending on the value of the data.

Have u studied the features of your DB to perform this? For example MSSQL2005
has support of paging.

j> However I cannot
j> process this value in SQL I need to perform some complex calculations
j> that can only be done in code.

Are they really so specific? Which DB do u use that you can't move it to db?

j> My question is this, is there a better
j> way than returning all the rows (just the values I need not the
j> entire row) run the processing query and then display 10 records? It
j> just seems a bad way to get 10 records!!!

So, if you need to calculate smth where all rows involved there is no other
way.
Can u describe what are u trying to do?

You can tune a process of data consuming using DataReader instead of DataSet

---
WBR,
Michael Nemtsev [C# MVP] :: blog: http://spaces.live.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsch
 
Hi Jose,

What do you mean by "complex calculations"?

If you're using Sql Server 2000 there are some mathematical and statistical
aggregate functions that you might be able to use. Control-of-flow language
elements are available as well, such as IF and WHILE.

Sql Server 2005 hosts the CLR so you can define stored procedures in C# if
you want.

"Using CLR Integration in Sql Server 2005"
http://msdn2.microsoft.com/en-us/library/ms345136.aspx

"CLR Stored Procedures"
http://msdn2.microsoft.com/en-us/library/ms131094.aspx

If you're not using Sql Server 2005 then you can try to limit the number of
records returned based on filter criteria extracted from the full algorithm,
if possible, and then run the full algorithm on the client. If you can't
find any simple filter criteria to use then I don't think you have any
choice but to pull all of the records for processing on the client.
 
Hi All,

(I realise this isn't a specific dotnet question but I'm using dotnet
to implement it.)

I have a table of data that could run to about 50,000 rows. A user will
enter a some data in a web form and I need to return a paged set of
results depending on the value of the data. However I cannot process
this value in SQL I need to perform some complex calculations that can
only be done in code. My question is this, is there a better way than
returning all the rows (just the values I need not the entire row) run
the processing query and then display 10 records? It just seems a bad
way to get 10 records!!!

Thanks in advance for any suggestions.

Jose
Click to expand...

Look at the ROW_NUMBER() function on SQL Server:
http://davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx

Cheers,
Gadget
 
Hello Jose,

If you share some of the details of your "complex calculation," I think you
will discover that some of the folks on this helpful board are able to show
you how to precalculate many of the values that you then need to use in your
search criteria.

For example, say you want to find all invoices where the average value per
line exceeds $100. Let's say you have 50,000 invoice headers and >200K
invoice rows. It would be prohibitively expensive to run this query without
precalculation.

On the other hand, if you simply added a column to the invoice header table
that stores the average value per invoice line for the entire invoice, then
the query is not only fast, but completely trivial. You simply have to
perform the calculation in advance, whenever the invoice itself is written.

Clearly, this is an overly simplistic example, and it may not be anything
like the problem you are facing. On the other hand, without further
information, you leave folks like us to guess, and we cannot be completely
helpful.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
 
Thanks for all your emails.

In retrospect it was quite cryptic to say "complex calculations"!!
Basically I am storing longitude and latitude data for various
geographical sites. This may imply that there may not be many rows in
the db but each site can have a number of items. A user can enter
his/her address and say "show me all the sites within 200 metres of my
location". I am using a 3rd party component to calculate the distances
between the user's location and that of the site - that is why I cannot
move it to the DB (or so I am assuming).

I am using ASP.net 2.0 and SQL 2K.

Thanks again,

Jose
 
It's worth telling people which version of SQL server you're using when you
ask a question so they don't waste time helping you with an inapplicable
solution.

If your coordinates are stored in a numeric format then you don't need an
external function as it's simple trigonometry, and you can narrow your
comparison down to a 70% match probability before doing any trig math by
looking at the square around your required location. This would use any
index on your X and Y coordinates, making it very fast.

If you're not storing your coordinates as numeric values then you should
be, and you could do this via computed columns, or by setting them when
loading data externally.

What Datum are you using for your coordinate data?

Cheers,
Gadget
 
It is certainly possible to do distance calculation from lat/long data in a
Stored Procedure. There are a variety of techniques for doing this, some of
them relatively simple and less accurate, and some of them relatively
complex and more accurate. However, you would not be able to use your
3rd-party component to do this. You would have to either find or write the
algorithms for yourself. If you want to do this, I can make some
suggestions.

--
HTH,

Kevin Spencer
Microsoft MVP
Logostician
http://unclechutney.blogspot.com

Parabola is a mate of plane.
 
Again thanks for the replies.
some of them relatively complex and more accurate...I can make some
suggestions.
Click to expand...

If you could make some suggestions that would be very useful.

Thanks,

Jose
 
Again thanks for the replies.


If you could make some suggestions that would be very useful.

Thanks,

Jose
Click to expand...

Well without some more information you won't get many more suggestions :)

What datum are you using and what is the table structure?
What is the format of data you are using to query the proximity?
Why are you currently using a third party component? i.e. What's special
about it?

Cheers,
Gadget
 
Back
Top