Design Issue

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

re: Design Issue

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.

--
Dave Sexton

"Jose" <discussions@avandis.co.ukwrote in message
news:1164659553.438745.267250@45g2000cws.googlegro ups.com...

re: Design Issue

Hello jose,

jI have a table of data that could run to about 50,000 rows. A user
jwill enter a some data in a web form and I need to return a paged set
jof 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.

jHowever I cannot
jprocess this value in SQL I need to perform some complex calculations
jthat can only be done in code.

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

jMy question is this, is there a better
jway than returning all the rows (just the values I need not the
jentire row) run the processing query and then display 10 records? It
jjust 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 Nietzsche

re: Design Issue

On 27 Nov 2006 12:32:33 -0800, Jose wrote:
Look at the ROW_NUMBER() function on SQL Server:
http://davidhayden.com/blog/dave/arc...2/30/2652.aspx

Cheers,
Gadget

re: Design Issue

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.
--
"Jose" <discussions@avandis.co.ukwrote in message
news:1164659553.438745.267250@45g2000cws.googlegro ups.com...

re: Design Issue

You can write complex calculations in a Stored Procedure.

--
HTH,

Kevin Spencer
Microsoft MVP
Virtual Carpenter
http://unclechutney.blogspot.com

Paranoia is a state of mind.


"Jose" <discussions@avandis.co.ukwrote in message
news:1164659553.438745.267250@45g2000cws.googlegro ups.com...

re: Design Issue

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

Kevin Spencer wrote:

re: Design Issue

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

On 28 Nov 2006 10:51:25 -0800, Jose wrote:

re: Design Issue

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.


"Jose" <discussions@avandis.co.ukwrote in message
news:1164739885.296324.249770@n67g2000cwd.googlegr oups.com...

re: Design Issue

Again thanks for the replies.
If you could make some suggestions that would be very useful.

Thanks,

Jose


Kevin Spencer wrote:

re: Design Issue

On 29 Nov 2006 10:30:23 -0800, Jose wrote:
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