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 |
|
sherrer
64 Posts |
 Posted - 2002-11-18 : 13:42:55
|
| I am attempting insert a fairly large amount of data, and I am getting the following error: Server: Msg 8621, Level 17, State 1, Line 1Internal Query Processor Error: The query processor ran out of stack space during query optimization.I have data being processed in VB and in an attempt to get the data to the server all at once, instead inserting it row by row, I am using the following SQL statement...Insert Into T1 (C1, C2, C3, ...)Select V1, V2, V3 ... UNION ALLSelect V1, V2, V3 ... UNION ALL...Is there a way to adjust the stack space on the server?Is there a better way to accomplish my task?I have done similar things in the past, with even more data and not had a problem.Thanks |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-18 : 13:48:12
|
| Are you selecting data from one table and inserting it into another? Or are you passing user-inputted data from a VB application? What kind of data is being passed exactly? Need more detail. |
 |
|
|
sherrer
64 Posts |
Posted - 2002-11-18 : 14:00:14
|
| The application is a billing software. The billing is done in batches of say 500 accounts to 2000 accounts. Each account may produce 1 to 5 transactions that need to be stored into a history table. So I may need to insert as many as 10,000 rows in one operation. Currently the application uses an ADO disconnected recordsets and commits after each account is processed. Speed is my only objective right now, and after tying many ways of getting data from VB, this seems to be the fastest way to do it. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2002-11-18 : 14:03:53
|
| Which version of SQL Server are you running? Do you have the latest service pack installed? I took a look at the Knowledge Base and although I didn't see an exact article that relates to your problem, it appears that similar problems have been corrected by installing the latest service pack. |
|
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-11-18 : 14:10:22
|
| Have you tried bulk inserting the data and using stored procedures to process it?How much memory on the SQL Server?How much is used when your process is running?Post some of your ADO code so we can see what you're doing. |
|
|
|
sherrer
64 Posts |
Posted - 2002-11-18 : 15:50:31
|
| I have SQL 7.0 with SP2. I have not moved to the the newest because I have a whole company dependent on the operation of the database. We will have to upgrade our test environment to see if the service pack breaks anything like SP2 did when we installed it, but I can give it a shot.As far as BCP, I can give it a try... I was trying not to write out a text file although, I don't know how long that will take. I need to try and have the data written in 10 to 20 seconds.We have 4GB of memory on the server, and the memory doesn't do as much as blip when the query is run. I can't find any documentation, but I suspect that the stack is static. My other option is to send the data in chuncks.Here is a code snipit. This code builds a delete statement to purge data from a preview table. I know this code is slightly different than the above example, but the results are the same. 'Purge Data from Preview Tables For i = 0 To iMaxAcctInfo sDataList = sDataList & "Select '" & aAccountinfo(i).AcctID & "' UNION ALL " Next sDataList = Left$(sDataList, Len(sDataList) - 11) sSQL = "Delete From InvoiceHistPreviewDetails Where AcctID IN (" & sDataList & ")" lResult = oConn.ExecSQL(sSQL)Thanks for the advice, I will give some of this a shot and reply back if a solution is found. |
|
|
|
sherrer
64 Posts |
Posted - 2002-11-18 : 17:07:22
|
| I tried upgrading to SP 4, and I still get the error. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
sherrer
64 Posts |
Posted - 2002-11-18 : 17:27:28
|
| Thanks for the links, I was actually about to write some code to test exactly that. |
|
|
|
sherrer
64 Posts |
Posted - 2002-11-19 : 15:24:07
|
| After playing around for many hours, I finally found that using Bulk Insert is a extreamly efficient way to move large amounts of data out of an application to the server. It was much faster than building the sql statment (like orginally stating) or passing CSV data to a SP, but this actually due to Visual Basic's pitiful handling of large string data, and not due to the performance of the server. Though untested, I do believe that a C++ application could build a CSV format much faster, and that it would be a little cleaner than having to build and manage text files.Thanks for all the advise. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-19 : 15:36:47
|
| Have you tried the VB Join function?Dim a As String, b(4) As Stringb(0) = "A"b(1) = "B"b(2) = "C"b(3) = "D"a = Join(b, ",")MsgBox aIt works like the VBScript/JavaScript/Java method(s), and joins the elements of an array into a string, using any delimiter you choose. It's much faster than using the & operator to concatenate.Also take a look at this article:http://www.vb2themax.com/HtmlDoc.asp?Table=Articles&ID=30And the rest of the site, for that matter. They have A TON of advanced string handling tips for VB. |
|
|
|
sherrer
64 Posts |
Posted - 2002-11-19 : 16:18:03
|
| Thanks robvolk, I was unaware of Join and it is fast. Now if I want to use your method, I have another problem. I have read your artical on CSV parsing (at least 5 times now), and I understand what you are doing. I still don't know how to accomplish what needs to be done without creating a cursor.Here is my VB code: Dim a() As String ReDim a(iMaxBillHistData) Dim b As String For i = 0 To iMaxBillHistData sDataList = Right$(Left$(sBillProcessID, 37), 36) & "," & j & "," & _ Right$(Left$(aBillHistData(i).AcctID, 37), 36) & "," & _ IIf(aBillHistData(i).CappedAmt > 0, aBillHistData(i).CappedAmt, aBillHistData(i).Amt) & "," & _ aBillHistData(i).RevCode a(j) = sDataList j = j + 1 Next b = Join(a, ",")I am storing the record in sDataList with the fields comma delimited and stuffing sDataList into an array. As you can see, I am joining the array into one variable to create a large comma delimited variable. Now I need to get the data into a table broken down into 5 column records. That is where I have no idea what to do.Thanks |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-19 : 17:47:59
|
Well, from what I can gather, you're using a comma to delimit both rows and columns, which won't work properly. You need to use two different delimiters, there's a basic description here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=19846I'm assuming that you're storing the data in some kind of user-defined data type, and using an array of that type, correct? I just hope it's not an object...I'm gonna try a different version of the code you provided, I don't know if it will perform better but it should work:Dim a() As String, b as String, c(5) as StringReDim a(iMaxBillHistData) c(0) = Mid$(sBillProcessID, 2, 36)c(5) = "~" For i = 0 To iMaxBillHistData c(1) = j c(2) = Mid$(aBillHistData(i).AcctID, 2, 36) c(3) = IIf(aBillHistData(i).CappedAmt > 0, aBillHistData(i).CappedAmt, aBillHistData(i).Amt) c(4) = aBillHistData(i).RevCode a(j) = Replace(Join(c, "|"), "|~|", "") j = j + 1 Next b = Join(a, ",") This avoids the dreaded & operator, but since there aren't that many elements being concatenated I don't know if it will actually run faster. This will generate a large string with each column delmited by pipes (|) and each row delmited by commas (,). You then perform a double parse in the SQL procedure, the first one parses out rows using the comman, then 2nd parse works on each row using the pipe to split the columns.I can probably work out the SQL for it but I need to see some examples of what you're passing to the stored procedure, and if you can clarify exactly how you're storing the data (how does this stuff get input exactly? I've not seen your approach used that often...just curious) Also, what does the j variable do? Is it actually different from i?Also keep in mind that if the length of the strings being passed to SQL Server exceed 8000 characters, you'll need to modify the code to accommodate that. You can use multiple parameters...in fact I would suggest that each column be passed as a separate CSV parameter, similar to the Flintstones example in the earlier link. You may also want to think about using fixed-length values, since these are all numerics anyway, and it's easy to create fixed-length strings in VB, and then you don't have to worry about trailing delimeters and such.Edited by - robvolk on 11/19/2002 17:49:58 |
|
|
|
sherrer
64 Posts |
Posted - 2002-11-19 : 19:14:41
|
| Rob,I truly appreciate the time that you have spent, I have learned more about SQL Server in the last 2 month's reading yours and others replies to questions than I have in the last 6 years that I have been working with databases as a developer. The major thing I have learned, is don't deal with data in small samples (like single records). I have a billing application and database that was designed for our company about 3 years ago. We bill over 100,000 utility accounts monthly and our current software bills at about 1000 accounts per hour. The code actually makes over 70 sql calls to the database per account to read and write the data required to produce a bill. We bill in groups of accounts anywhere from 100 to 2000, so I am rewriting the system to handle each group as an entity. The data is called in from a stored procedure that returns about 15 seperate result sets. They are loaded into array structures (no not objects... too fat and slow), and yes each array structure is a single diminsion array of an enumerated type. I am very happy with loading the data. I use a server side cursor which loads the data into the arrays while the data is arriving at the application server. The data is now being loaded in less than .5 seconds. Our billing process is quite complicated, and is process intensive which is why we are not doing it on the server. The bills are produced in .5 seconds for my test set of 600 accounts. After the data has been processed I am trying to get it back to the server as fast as possible. Using Bulk Insert I am getting it loaded in .2 seconds which is going to blow our management away. My objective was 100,000 accounts per hour, and now I am approaching 1,000,000 accounts per hour. I am uneasy dealing with the text files, so I want to work out the CSV approach. To answer about the j variable, it was left in from another approach, sorry to confuse you on that. I also left the concatenation of the variables becuase I was getting lazy at that point, and figure it was small enough not to affect me much. I will test your approach; the faster the better.Why do you suggest loading the columns as seperate variables? I am very concerned about speed of processing in the stored proc, if I can beat .5 seconds then I am doing no good. Also, I have quite a few tables in the end that will need to be processed like this, some data will need to be inserted while other data will need to be updated.Also, I am trying to figure out if it would not be better to pass the CSV data by inserting it into a table instead of processing a variable?I am going to work on the code from what I know and the examples that I can find. I can post some code once I get it working. I would rather do that at this point than let you hand feed me the SQL; then you can criticize what I get in the end.Thanks again for all your help. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-11-19 : 19:32:35
|
| If you get that kind of performance using BULK INSERT or bcp, then stick with it. I was thinking in terms of not needing an intermediate file to import, but if it works well, then don't change it. If it really needs a major rewrite on your part it's probably not worth the time to change it.Keep an eye out though, going from 600 rows to 100,000 rows probably won't scale linearly. Also watch out for logging, it shouldn't be a problem with BULK INSERT, but if you migrate to SQL Server 2000 and use the Full recovery model, it could really slow down. It's hard to say if passing CSV's will be faster, you'll have to test it. The INSERT operation would also be logged, so it's very likely it will be slower.My point about having each column passed as a separate variable comes from this:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12538In that example, all the firstnames are passed in one variable, all the last names in another, etc. That way, there's no possibility of something being parsed incorrectly, so that a last name could end up in the first name column. This is especially true if null or empty values can appear in a column, you have to be extra diligent about that when parsing the string. Having separate variables for each column makes this a little easier and less prone to error.Is there any chance that the logic for processing the bill could be moved to SQL Server? Even if the process involves some extensive process that can't be converted to SQL, if it can be encapsulated in a COM object you can call it from SQL Server. That would eliminate the network traffic, which is probably your biggest speed killer (especially if it's making 70 database calls per batch) 1,000,000 rows per hour would be NOTHING. And you wouldn't have to reload anything back into the database.If that's a possibility, you can put the entire thing into a SQL Server job and schedule it to run automatically too. There's a whole raft of things you could do if the logic can be consolidated onto the SQL Server itself. |
|
|
|
sherrer
64 Posts |
Posted - 2002-11-19 : 20:23:17
|
| I will play some with the CSV, but if I am not done this evening I will stick with the BCP. I knew with BCP it is not being logged, which will also cause us to change our backup procedure since the log dumps fail after an unlogged insert has been done.You are right about the process probably being more effiecient on the server. I have 20 years development experience (and I am a little embarrassed that I did not know about JOIN in VB, but only a little with database processing (I am learning though). I am going to stick with the guns, since I have a dead line to have a production release before Jan 1. We will be migrating to SQL 2000 by Q3 next year, and I am looking forward to making our product even better. As far a network traffic, I have an application server hardwired to the database server on it's own subnet with 1GB NIC cards. I don't think the network will even break a sweat. Currently it is a big deal, since all the processing is done by client machines by account, with the new code I am working, I queue the process out to the application server which makes one call to the database to get the data and about 5 to get the data back for a entire group of accounts.Thanks for all your help and discussion. |
|
|
|
ComprarVmx
Starting Member
2 Posts |
Posted - 2012-01-24 : 04:09:39
|
| unspammed |
|
|
|
|
|
|
|
|
Internal Query Processor Error: HELP