| Author |
Topic |
|
craigmacca
Posting Yak Master
142 Posts |
 Posted - 2011-04-26 : 08:05:07
|
| Hi i have a table which data is inserted and then a .net scheduled task processes the data row by row and the deletes as each row is processed.if my table contains 10,001 records and the 10,001st record takes a good few minutes to be handled. i have optomised the .net schedule task as much as possible, so i am now looking at having 5 tables instead of 1.what i need is someway of having a limit on the tables, so when the data is inserted if the table has hit its limit it will then insert into table 2, table 3 etc.I can do this by doing a count on the table first i.e. if count > 500 etc, i am just wondering if there is a better way of doing this? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-26 : 08:07:36
|
| Have you looked at indexes - it sounds like you are table scanning.If you need to split the table look at partitioned tables which will do it for you.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2011-04-26 : 08:38:41
|
| i would need to do the following...1. Create 5 tables "TableA", "TableB", "TableC", "TableD" "TableE"2. Insert row of data - if "TableB", "TableC", "TableD" "TableE" has 1 record and "TableA" has no records then the row will be inserted into "TableA" so the data will be split accross these tables evenly.Is this how partitioning works? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-26 : 09:10:14
|
| Sort of.You partition a table using a partitioning column - in your case probably an identity if you want it to work like this.Then you create partitions using a range function. The table is actually created as many tables and the range function used to access the underlying tables. So you don't change the queries but the optimiser decides which table to access depending on the value of the partitioning column.You should investigate indexing first though because you are dealing with tiny numbers of rows here==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2011-04-26 : 09:40:08
|
| ok thanks i dont think this will work for me, i need to have 5 different scheduled tasks running which will process data per table i.e.net Schedule1 will handle data from tableA.net Schedule2 will handle data from tableB.net Schedule3 will handle data from tableC.net Schedule4 will handle data from tableDso i will need to have the tables already setup. the schedules will then run every minute which means data will be processed Simultaneously..net Schedule5 will handle data from tableE |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-26 : 09:45:22
|
| Well you could use partitioning for this - it's actually easier.Just have the partitioning column having integer values and the integer being the partitioning column value.Then the apps insert into the same table but each se a different partitioning value.If you want to manually create the tables separately then you can - I don't see what your problem would be as you are handling it from the applcation rather than the database.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-26 : 09:46:46
|
| >> i have optomised the .net schedule task as much as possibleI suspect this is your issue - have you done anything about the database efficiiency.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2011-04-26 : 10:08:43
|
| >> i have optomised the .net schedule task as much as possiblethe .net schedule pulls all the current data from the table using a basic select statement, then loops through the recordset row by row.in the loop we then connect to a smpp server to send out sms and then delete the current row.if there are 10,000 rows this takes awhile. so i am thinking to create 5 tables instead of 1. unless there are any other ways i can do it? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-26 : 10:19:53
|
| So it's an email table.I wouldn't use separate tables.Just have a status column on the tableAccess the table via an SP.Each version of the app has an IDThe table has columns appid and updateddateSimplest wayThe app calls the SP with it's ID and action getThe sp checks the table - if there is a row already allocated either error or return that row againThe SP locks the tableUpdates the next free row with the AppID and updateddatereleases the lockreturns the row to the appThe app calls the SP with it's ID and action deleteThe SP deletes the row allocated to that AppID (or updates to complete)In this way you can add aps as you wish - doesn't matter how many you run.I would also keep a record of what they have done.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2011-04-26 : 12:02:32
|
| No its not an email table its a sms table all the rows need to be processed and sent out as an sms. if there are 10,001 sms to send then its takes ages to send the 10,001st, i need a way to split the data into more tables so i can have the sending of sms in 5 processes running at the same time. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-26 : 12:24:19
|
| sms - email, similar thing.Did you read the post?You don't need different tables to drive applications.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2011-04-27 : 04:33:59
|
| Yes but i only have one app, which is a web app. this sends the sms to a table so they can be sent out. if a the user sends 10,000 and another user sends 1 sms after the 10,000, then i takes ages for the 1 sms to be sent.so appid would not work, athough maybe i could do this every time a group of sms is submitted by a user i include a group id, then have a status table i.e.sms_status_tableIdGroupIdInUsesms_process_tableIdGroupIdOther Columns etcThen when i process the sms i can have 5 seperate schedules running every minute that gets the group of messages. The only issue i see is would there ever be a case where the SP below would be called at exactly the same time, that will cause the selection of the same records?--first get the top 1 Id from the sms_status_table where InUse = false--set the selected top 1 InUse to true--return the selected top 1 sms_process_table results |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-27 : 05:23:54
|
| >> that will cause the selection of the same records?The way you have done it yes - see my coment about locking when allocating the row to an app.>>.net Schedule1 will handle data from tableA.net Schedule2 will handle data from tableB.net Schedule3 will handle data from tableC.net Schedule4 will handle data from tableD>>Are these not different instances of the app. Still multiple copies of the table doesn't help.Your issue is just that you are getting a lot of rows inserted for a single user so all you need is logic to make sure that one user doesn't hog the process.Keep a record of the users that are processed and make sure it cycles. i.e. process one row for each user in the table before processing the first user again - I take it you have a record of the user that entered the row?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2011-04-27 : 05:58:11
|
| ok yes that nakes sense now, so..**Currently i have**one .net schedule which gets all the records in the table and loops through each row and processes the sms**New Solution**1. one .net schedule which has a while loop2. SP which gets the top 1 row and passes in @UserId (this could be null if the first record) this gets a record that <> @UserId unless there are no other records found.3. store the userId from the SP 4. process the sms 5. delete the record6. go to next loop row and pass in the stored useridi think that would work? |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-27 : 06:07:44
|
| It will work unless there are two users that add large batches in which case it could just flip-flop between them and any other users will wait.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
craigmacca
Posting Yak Master
142 Posts |
Posted - 2011-04-27 : 06:21:46
|
| ok thanks, i have around 500 users so i would need to solve that issue, any ideas? |
|
|
|
|
multipul data tables