|
Trekstuff wrote: and return a few million records, I need to display that data in an ASP.NET Grid
Your requirements are badly flawed.
If you have one million records and a user looks at each record for only 1 second it would take them 7 weeks (8 hour days at 5 days a week) to look at the list.
|
|
|
|
|
I apologize, I must've not made my intentions clear. User won't need to see all those few million records at once. He/she would need to make ad-hoc request to this dataset: filter/sort/search it on the fly and expect results fast.
Thanks for the math lesson though 
|
|
|
|
|
Then the best way is to ask the user for the criteria and then build an appropriate query from that.
|
|
|
|
|
User may have dozes of different criteria he/she would want to run ad-hoc. Those criteria aren't know in advance and need to be passed to data on the fly.
|
|
|
|
|
Trekstuff wrote: <layer>User may have dozes of different criteria he/she would want to run ad-hoc.
May and will are different.
And standard business users will not have those requirements.
For example anyone dealing with customers only needs a couple of criteria to find the the customer/order.
And if you really meant "ad-hoc" then you have a power user and they should be proficient in SQL and have a SQL application, not a created app.
|
|
|
|
|
Yes I am convinced. I can see it now: A bunch of execs at a board meeting firing up SSMS and just querying happily away
|
|
|
|
|
Trekstuff wrote: Yes I am convinced. I can see it now: A bunch of execs at a board meeting firing up SSMS and just querying happily away
If they want unlimited queries that is exactly what is needed and wrapping it in another app doesn't change that.
And that is exactly the situation at a bank that I worked at. The VP in charge of the merchant credit services often (more than once a week) did custom queries directly into the production database.
|
|
|
|
|
jschell wrote: If you have one million records and a user looks at each record for only 1 second it would take them 7 weeks (8 hour days at 5 days a week) to look at the list.
..assuming that they're looking at the records themselves, and not some kind of visualization of those records (like a chart) - that's assuming that they're looking at the data at all; for all I know they could be preparing an export to some Microsoft-Access database.
Bastard Programmer from Hell 
|
|
|
|
|
This seems like a bad idea; no one is ever going to look at a million plus records; at best they'll look at a small subset. At the very least get them to filter before going to the database and only ever return as many records as they actually need to see, perhaps restricting to a managabale number.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
That's the point, I don't know in advance what filters are going to be used; I can only restrict the data somewhat by initial call too the SP; but I cannot call SP for every filter request - this call is to expensive.
For example: user needs to work with data for the past 3 month - this Date filter I can pass to SP and this call can returns several million of records. Once the data is obtained - user may want to: Get only rows which "Name" field start with "D" or which SSN ends with 74 or group by LastName and get the count etc. etc. - these requests will limit data to a much smaller subset, but are unpredictable and made to the original large resultset returned by the SP - instead of SP itself
|
|
|
|
|
Not getting your reasoning here: if they first filter on the last 3 months why can't they also filter on other criteria at the same time? If it doesn't give them what they want they start over. Again, only retrieve the records they really need - it's more sensible to build a filter screen and get what you need than not and retrieve far more than they'll ever look at.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Again, the only condition they know in advance is "last 3 months". It is passed to SP - which is expensive to run, it may take several minutes (and please don't ask me to optimize it, it's not an option at the moment).
Once the data is there - it needs to present a flexible, dashboard view. User clicks the Name column to filters data, observes result, uses it elsewhere, removes the filter. User searches for SSN that ends in 74, and uses that data, removes the filter. User sorts data by date, jumps to the 1st page, jumps to the last page.
Again, these requests cannot be passed to SP - it will take too long, they need to be done to subset of data, returned by SP (yes those several million of records) that presumably are stored in some local storage.
|
|
|
|
|
Trekstuff: We might have the same case as you are. You didn't describe what 'the expensive SP' is doing, but in our case it is a cross-tab complex query of about 5 million rows of data. In our case, the time periode is always one month, so we have SQL Agent services that would run the query at 23:00 on each last day of the month. The result then stored in permanent tables on different database. The user would then query their requirement to these tables. The result is satisfying. We just have to 'torture' the server for about 3 to 5 hours on that night. Of course the downside is there are always a specific requirement that the available data warehouse couldn't comply. But we have a policy that they have to request their 'custom' need first to the IT department, and wait for the result for at least one day. That way, we can still have our tea time
hth,
foxyland
|
|
|
|
|
This helps indeed. More and more I am getting convinced that separate DB with permanent tables is the way to go. Thanks guys you're the best.
|
|
|
|
|
I have a VB program that uses SQL Server as a database. One of the tables has a primary key, a serial number, that is inserted as part of the insert statement. The VB program determines the correct serial just prior to creating and executing the insert statement. The serial number not an incrementing value but is an aggregate of several pieces of information. Since I have copies of the same VB program running at once there is a very small window for trouble in which both clients could attempt to insert with the same serial number. This is very unlikely but still possible. So I'm wondering if there is
(a) A way to move the serial number calculation code to the server and take the serial number out of the insert statement.
(b) Having a taken the serial number out of the insert statement, for the VB client to have an iron clad way retrieving the serial number that it just created (most recently created record wouldn't be sufficient since it would run into the same vulnerability of multiple clients trying to do the same thing at once).
|
|
|
|
|
|
Thank you responding. I will read through the documentation completely. But just at a first glance wouldn't this run into the issue of 2 clients inserting at the same time but then they both fetch the same serial number. To put it another way, client A should get 12345, client B should get 12346, but due to the timing of the inserts they both get 12346.
|
|
|
|
|
a new connection implies a new scope, SCOPE_IDENTITY will not return identities created through other connections.
|
|
|
|
|
As Luc has already answered on my behalf, SCOPE_IDENTITY() is scoped to your connection and ensures that you do not get a value that was inserted through another connection even if it were newer. There are other ways to read identity values though, but I suggested this method specifically for this reason.
|
|
|
|
|
I strongly agree with getting that off the client.
How to do so may depend on exactly what information is used to generate the serial number. I've seen some that use the date and a sequence, e.g. 120313123 (YYMMDDseq), with the sequence rolling over each day. Something like that could be done in the database, by a stored procedure. I understand that the upcoming version of SQL Server will have sequences built in (Oracle has had them for decades), but you could also create your own sequence (which is what I do when I need a sequence).
On the other hand, you probably shouldn't put any "information" in the serial number in the first place.
As to using identity columns... I don't; I find them to be very problematic, and a simple sequence works much better in most cases.
|
|
|
|
|
PIEBALDconsult wrote: As to using identity columns... I don't; I find them to be very problematic
I've been using them for over a decade and haven't really found any problems.
|
|
|
|
|
I have used them infrequently -- only in apps that someone else wrote -- and have always had trouble.
Using a sequence or GUIDs has never caused me trouble.
|
|
|
|
|
Shameel wrote: I've been using them for over a decade and haven't really found any problems.
Neither have I.
If I remember I will check my SQL 6.5 reference to see what I was using with that (since I have been using SQL Server longer than a decade.)
* UPDATE * Yep 6.5 had them so I haven't had any trouble using them for 15 years.
modified 17-Mar-12 13:32pm.
|
|
|
|
|
As others have implied you need to change your strategy. Personally I would leave the serial number method exactly as it is but would not have it as the primary key. A primary key should hold no intelligence in it's data, so create another field (based on IDENTITY or GUID spit)) and use the serial no as a piece of clients code.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
First of all, separate the serial number and the primary key. If you use a Guid as the key, you can "calculate" it (Guid.NewGuid()) already on the client. In case of an autoincrement value, you can query it from the database with a SELECT @@ID.
Your serial number column should still have a unique index.
When you insert your data, omit the serial number first.
You can take two different approaches here:
(1) Create a trigger. In the trigger function, calculate your serial number. Take care that two calculations may happen at the same time in different threads. The trigger function then updates your data. Retrieve the inserted serial number with a select query.
(2) Calculate the serial number on the client. Do an update query. When the update fails due to a duplicate value, calculate again with adjusted parameters, and try again, till the update does not fail. Here, I'd use a transaction, and commit when the update was succesful. Looks bad, but with a low chance of duplicates, it will not cause bad performance.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
