I'm only processing 50,000 records not everything from the Table where there are 250,00 records.
What have I done wrong in code
Thanks.
DECLARE @BatchSize INT = 50000
DECLARE @IdControl INT = 1
DECLARE @Results INT
DECLARE @SQLString NVARCHAR(MAX);
DECLARE @ParmDefinition NVARCHAR(MAX);
WHILE (@Results IS NULL OR @Results > 0)
BEGIN
print @IdControl
print @BatchSize
print (@IdControl + @BatchSize)
-- Insert records in a batch
Select @SQLString = CAST(sqlToExec AS NVARCHAR(MAX)) from
CB_Staging.dbo.crtinsert where
ID > @IdControl AND ID <= (@IdControl + @BatchSize) order by ID;
Print @SQLString
-- Execute the SQL command
Begin Try
EXECUTE sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Error handling code
SELECT
ERROR_NUMBER() AS
ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
-- You can log the error details or take other actions as needed
END CATCH
-- Update the @IdControl for the next batch
SET @Results = @@ROWCOUNT
SET @IdControl = @IdControl + @BatchSize
Print @IdControl
END
April 26, 2024 at 1:14 am
NVM
April 26, 2024 at 1:20 am
Okay I cannot say if what I am outlining will help much but I have found it is better to Keep It Simple and Smart (K.I.S.S.) rather than getting complex or fancy-dancy.
SET @SQLString = CAST(sqlToExec AS NVARCHAR(MAX))
-- Insert records in a batch
SELECT @SQLString
FROM CB_Staging.dbo.crtinsert
WHERE ID > @IdControl
AND ID <= (@IdControl + @BatchSize)
ORDER BY ID;
So as you can see you do not declare what "sqlToExec" is supposed to be. Also I am only hoping that "@IdControl + @Batchsize" has a quality meaning as that is unclear by simply looking at the script.
April 26, 2024 at 10:37 am
thanks
The sqltoexec are passed in sql statements like:
INSERT INTO cstDetails (CompPartNbr,PartNbr,PlantCd,CostKey,usgQty,scrQty,totQty,UnitCost,usgCost,scrCost,totCost,StrJCost,StrMCost,SetUpQty,SetUpCost,CurrentOPRSetup) VALUES('967120411','864A90D40','CORP','Standard',0.2697,0.0043152,0.2740152,6.5,1.75305,0.0280488,1.7810988,0,0,0,0,0)
The CrtInsert table has a PK field called ID(int)
In the example how do I loop thru thousands of records
April 26, 2024 at 3:54 pm
I think your code will set the @SQLString variable to the value of the last row in the batch. If you want to execute multiple rows in a single SP_EXECUTESQL then you have to combine them. Try setting the batch size to 1 to see if it works. You might have a problem with single quotes, unless the sqlToExec values are already correct to use with dynamic sql.
-- This fails
DECLARE @sql VARCHAR(1000)
SET @SQL 'INSERT INTO cstDetails (CompPartNbr,PartNbr,PlantCd,CostKey,usgQty,scrQty,totQty,UnitCost,usgCost,scrCost,totCost,StrJCost,StrMCost,SetUpQty,SetUpCost,CurrentOPRSetup) VALUES('967120411','864A90D40','CORP','Standard',0.2697,0.0043152,0.2740152,6.5,1.75305,0.0280488,1.7810988,0,0,0,0,0)'
Below is a really simple example using string_agg to combine the statements, and adding extra quotes to the insert. You could also try using this method to combine statements.
SET @SQLString = ISNULL(@SQLString,'') + sqlToExec + ';'
I think that trying to combine 50k into a single variable is unlikely to work and I don't know if it would be any faster than executing them one by one. If you want to proceed this way I would start with batch size =1 to see if it works, then try combining two and then trying 10, then 100 etc. Every insert will be a single execution, but maybe using batches will save some times by reducing the number of iterations of the while loop. I would do anything I could to get the data in a more usable format, like a flat file.
DROP TABLE IF EXISTS dbo.crtinsert;
DROP TABLE IF EXISTS dbo.crtInsertAgain;
CREATE TABLE dbo.crtinsert
( ID int identity(1,1),
sqlToExec VARCHAR(1000)
);
CREATE TABLE dbo.crtInsertAgain
(
InsertCode VARCHAR(100)
);
INSERT dbo.crtinsert VALUES
('INSERT dbo.crtInsertAgain (InsertCode) VALUES (''First Insert'')'),
('INSERT dbo.crtInsertAgain (InsertCode) VALUES (''Second Insert'')'),
('INSERT dbo.crtInsertAgain (InsertCode) VALUES (''Third Insert'')')
SELECT *
FROM dbo.crtinsert;
DECLARE @SQLString NVARCHAR(MAX)
SELECT @SQLString = CAST(STRING_AGG(sqlToExec,';') AS NVARCHAR(MAX))
FROM dbo.crtinsert
WHERE ID <10
SELECT @SQLString
EXEC SP_EXECUTESQL @Query = @SQLString
SELECT *
FROM dbo.crtInsertAgain;
April 26, 2024 at 5:07 pm
thanks The sqltoexec are passed in sql statements like:
INSERT INTO cstDetails (CompPartNbr,PartNbr,PlantCd,CostKey,usgQty,scrQty,totQty,UnitCost,usgCost,scrCost,totCost,StrJCost,StrMCost,SetUpQty,SetUpCost,CurrentOPRSetup) VALUES('967120411','864A90D40','CORP','Standard',0.2697,0.0043152,0.2740152,6.5,1.75305,0.0280488,1.7810988,0,0,0,0,0)
The CrtInsert table has a PK field called ID(int)
In the example how do I loop thru thousands of records
TBH, that's a bit crazy. It would be much better if they just sent the data as a CSV with "1 row per line". The earlier to fix these kinds of issues at the source, the better.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 26, 2024 at 6:01 pm
Jeff,
How would data being in csv be handled differently?
Thanks.
April 27, 2024 at 1:04 am
Jeff,
How would data being in csv be handled differently?
Thanks.
If they send the CSV data in a file, then the data could be imported using BULK INSERT. This would be done in a single shot instead of having one execution plan per row. If in the BULK LOGGED or SIMPLE Recovery Model, it could be a very high speed "Minimally Logged" operation even with a Clustered Index in place. (Table would have to start out empty, clustered index only, no non-clustered indexes, code would follow certain easy rules).
Even if the table already has data or NCIs, etc, which will make "Minimal Logging" impossible, it'll still blow the doors off of the single row INSERTs they're currently sending you.
The only issue (but same issue as the stuff you're currently using) is when you have Commas or Single Quotes embedding in text "fields". If you had SQL Server 2017 or above, that would be no issue. Instead, you could resort to a "TSV" format. That's just using TAB characters as delimiters instead of commas. Same nasty fast performance, though.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 27, 2024 at 11:33 am
I can easily make it a csv file for Bulk Insert and YES there will be data on the Insert statements that have:
VALUES('967120411','864A90D40','CORP','Standard'
Can you show me the frame work of the Bulk Insert process I would like to explore that option as well...
Thanks again to all..
April 27, 2024 at 12:35 pm
instead of trying now to follow a different approach why don't you tell us, very detailed, what you are trying to accomplish with having some software inserting those sql statements onto that table to be executed at a later stage.
then maybe someone will be able to suggest you do something different - likely a better solution than what you have now.
with regards to how to do it on a batch - YOU have ALREADY been told how to do it on a few posts above - so instead of ignoring what others post here, just because you may not understand what they told you, read them, try them and see what you get - although as others stated this is a BAD solution for a problem we know nothing about.
April 27, 2024 at 4:44 pm
I can easily make it a csv file for Bulk Insert and YES there will be data on the Insert statements that have:
VALUES('967120411','864A90D40','CORP','Standard'
Can you show me the frame work of the Bulk Insert process I would like to explore that option as well...
Thanks again to all..
I can but... take a look at Frederico's post above. You've told us how you're doing something but you've not told us what problem you're actually trying to solve.
Let's start with the very first question...
--Jeff Moden
Change is inevitable... Change for the better is not.
April 28, 2024 at 12:28 am
I concur with Jeff Moden and frederico_fons in that without presenting the entire problem in detail upfront you are short changing yourself on a quality solution. The less you provide the less you get in return, conversely the more you provide the more you get in return. If you need to mask the it then do so but provide in as much detail as possible of where the data is coming from and what its being done to it (if anything) and where that data needs to end up (complete with a table description). Once we have this information then we can more easily provide you with a more complete and quality answer.
April 28, 2024 at 11:43 am
There is an app that was sending DML requests from a SQL2016 to SQL2008, and we saw lots of issues and it could have been driver related where record locks and just overall slowness processing. We decided to create 3 tables to hold the Inserts, Deletes and updates
and process them from an SP called from the server\db where we want them applied. The tables created have 3 fields:
ID
SqlToExec
DateAdded
Now I would like to Loop thru these tables and basically execute the Field(SqltoExec) using a BatchSize of 50,000 records using the ID field to drive that process. In an earlier post I supplied what the Insert piece looks like that needs to execute.
I hope this explains a bit more of the overall process I'm looking for the most efficient way to process these 3 tables.
1) deletes
2)Inserts
3)updates
Thanks
April 28, 2024 at 12:38 pm
There is an app that was sending DML requests from a SQL2016 to SQL2008, and we saw lots of issues and it could have been driver related where record locks and just overall slowness processing.
remaining reply removed as not part of the problem
....
Thanks
Based on your reply its likely safe to assume this is a in-house app - how was it sending data from a SQL 2016 to the 2008 - explain with details please - including if by app you are talking about a set of SQL Stored Procs, SSIS or if truly is a independent application (c# or other) and where it was running.
with regards to slowness and record locking - yes it could be driver related (or better a possibility of NOT defining MARS on the connection string which can with some drivers mess up things) - but in all likelihood what you noticed is a combination of bad coding, missing/incorrect/too many indexes and server specs. Not much we can do here either without full discloser of the issues and how the code works.
this is what you should be focusing in, hiring someone that knows what they are doing, instead of trying something you ask for on the net which may not be the correct/desirable solution for your problem.
as for a possible solution on what you are trying now - you failed to give enough details - how many tables, are the inserts/updates always made with exactly the same columns or does it vary - but one thing is likely certain is that if this involves multiple tables a CSV option will not make your life easier - and would not necessarily perform better either.
a while loop (a implicit cursor) is also not the best solution here - for this purpose this would require you to aggregate multiple execs into a single variable and depending on size it can be rather slow to execute as well - a explicit cursor would be better and would allow full control/logging of what was done and failed.
and this solution you trying out now likely will not perform better than the original process done through the APP - just because you move a process from point A to point B will not solve server or indexes issues.
April 28, 2024 at 2:55 pm
I don't have access into the app ... The deletes and inserts all have same formats I showed Inserts above and the deletes
DELETE FROM cstSums WHERE PartNbr='132883D01' AND PlantCd='CORP' AND CostKey='Standard'
I tested the Inserts and Deletes by just doing a Select against each table for say 10,000 records using the ID field and plugged the results set into a query window and executed it was very fast on the Sql2008 machine and saw no evidence of blocking.. SO I know it's somewhere in the mechanism of the 2016 to 2008 ..
Now I just need an explicit cursor would be better and would allow full control/logging of what was done and failed. like you eluded too ...
THanks.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply