Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pcmech
Starting Member
7 Posts |
 Posted - 2002-05-28 : 23:11:31
|
| I have a table that will contain over 100,000 records, and over 100 users will access this table. Each user will use a predefined select statement to access unique records (no two users must access the same record). I am using a dynamic SP to run the select statement, however I am unsure how I can use this predefined statement to select and individual record, update this record (to show it is not a public record any longer), and then return the record to the user.When the user accesses the record I want a field (Public_Record – bit) to be updated to ‘0’ and the ‘Edit_Login’ field to be updated with the users Session Variable.I am using the following SP to retrieve to retrieve the Recordset:CREATE PROCEDURE usp_select_campaign_contacts (@WHEREClause varchar(8000))AS -- Create a variable @SQLStatement DECLARE @SQLStatement varchar(8000) -- Enter the dynamic SQL statement into then variable @SQLStatementn SELECT @SQLStatement = "SELECT Contact_ID, Org_Code, Company, First_Name, Last_Name, City, StateCode, Zip, Country, Phone, Create_Date AS Record_Create, StateID, Public_Record, Edit_Date, SicCode1, Edit_Login, Contact_Type_ID, ResultCodeIDFROM dbo.tblContacts WHERE " + @WHEREClause -- Execute the SQL statement EXEC(@SQLStatement)GOAny ideas on where I should go from here? I debated on selecting a random record, but due to the fact that I am using a Dynamic SQL statement for the select, I didn’t think that would be the best solution. |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 09:49:16
|
| Oh boy.You're headed quickly down the road of pain and bitterness. I'm glad you posted.1. Don't use dynamic SQL if you can possibly avoid it. The increased overhead in compilations is expensive and (most of the time) unnecessary. I don't know what efforts go into the creation of your @whereclause variable, but consider placing it inside the procedure along with conditional logic to run specific, able-to-be-compiled-ahead-of-time DML.2. You haven't mentioned how many rows this select statement is likely to return. One? More than one? With > 100,000 rows you'll absolutely want to avoid table scans. Make sure you're indexed properly.3. You haven't placed any logic in the procedure to guarantee two users don't grab the same row. There are several schemes for this; I prefer using isolation level - based logic with transactions.4. Never, ever ever ever ever ever think of "random" in the same space as "relational database". Doesn't exist, never did, never will.setBasedIsTheTruepath<O> |
 |
|
|
pcmech
Starting Member
7 Posts |
Posted - 2002-05-29 : 10:54:27
|
| I am forced to use a Dynamic Select because of how the application flows. Let me explain, I have admin personnel creating a query to create a ‘campaign’ of contacts. This query can include many parameters and is saved to a table in the database. Each user will then log in and begin to pull off leads (one at a time) using the query completed by the admin personnel (the query is pulled out of the relevant table by another SP). So to answer your question about the @whereclause – This data is pulled out of another table and all I need to do is pull in the data, thus reducing the overhead. This user must only deal with one record at a time.You mentioned using isolation level - based logic with transactions, how would I go about doing this? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 11:14:00
|
| Are you in the collections industry?setBasedIsTheTruepath<O> |
|
|
|
pcmech
Starting Member
7 Posts |
Posted - 2002-05-29 : 11:39:02
|
| No, the telcom industry. We need to make certain that each contact is only handled by one person (to avoid a contact being contacted more than once by different personnel). |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-05-29 : 11:42:37
|
quote:
No, the telcom industry. We need to make certain that each contact is only handled by one person (to avoid a contact being contacted more than once by different personnel).
Your dialer doesn't take care of that for you when the campaign is defined? Hmmmm.I consult in this industry, so if you'd like to talk more about your issues send me an email ( to boott@verizon.net ). I'd be more than happy to assist.setBasedIsTheTruepath<O> |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-05-29 : 11:56:18
|
| sounds like you need some additional data design....leads-----<staffleads>--------staffleads, staffleads, staff...all tables...1 to many relationship in the middle.staffleads...(columns) fkstaff#, fklead#, starttimedate, endtimedate uniqueindex on fklead#...means each lead can only be here once uniqueindex on fklead#, endtimedate...means each lead can only be dealt with 1 person at a particular time....trick to soling this problem is to ensure that your start/end times don't overlap for any one lead.sample data...staffleads table S1, L1, 9am, infinity (or 31/12/2099 23:59:59...or other large date) S1, L2, 9am, infinity S1, L3, 9am, infinity s1...therefore has 3 leads being worked upon. when finished with a lead...endtime filled in with the appropriate date/timei.e. above data becomes S1, L1, 9.00am, 4.30pm S1, L2, 9.00am, 5.00pm S2, L2, 5.01pm, infinity.....result shows that L1 was finished with altogether...whereas L2 was transferred to another staff member S2...but the system recognises that S1 was involved for a period of time.when selecting the records....for S1 in the 1st place...have a sub-query/join which ensures that no eligable leads have records in the staffleads table with an open (ie being worked upon/assigned) enddatetime.selecting these records and at the same time inserting new records into staffleads should solve your problem....insert into staffleadsselect a,b,c,d from leads where not exists (select * from staffleads where staffleads.fklead# = leads.# and endadate = infinity)or a form thereof......if you run this twice in succession....the 1st run will create records in staffleads, thus eliminating them from the 2nd run...HTH..... Andrew |
|
|
|
pcmech
Starting Member
7 Posts |
Posted - 2002-05-29 : 12:22:05
|
| That might work. I currently have a table for the ‘Users Contacts’ which I could easily modify to include this new data. In fact, this might solve my next issue of creating a user trail for each user. By using this method I can easily scroll through each individuals history without having to cursor through all the records.I will give this a shot. Hopefully, if this can process quick enough, the possibility of 2 simultaneous users accessing the SP at the same moment and getting the same record will be very small. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2002-05-29 : 12:31:17
|
| putting the 2nd index on the table will prevent duplicates being inserted!!!....all you have to do then is put code in to re-run the query in the unlikely event that 2 runs of the query produce the same leads...given that the 2nd insert will fail!!i use this type of schema a lot to allow me track changes to records....especially where i need to know who worked on an item at a particular point back in time....the key is to use a suitable infinity value....which basically means still being worked upon....and to replace it with the actual endtime when appropriate and to create a new record with an "open" endtime for the next record..... |
|
|
|
pcmech
Starting Member
7 Posts |
Posted - 2002-05-29 : 12:52:44
|
| What do you normally use for the suitable infinity value? Do you generally use a 'datetime' or 'smalldatetime' for the field value?I am using a Dynamic SP to run the select, do you know of the best way to re-run this query if data already exists in the new table? |
|
|
|
|
|
|
|
|
Dynamic Query with Update