5/14/2024 11:30:47 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss using the SalesLogix OLE DB Provider from external applications (including usage of built-in provider procedures). View the code of conduct for posting guidelines.
|
|
|
|
are there anythings to consider with transactions?
Posted: 24 Dec 08 2:02 AM
|
We've recently been having some problems with our site crashing...
We think it may have to do with our use of transactions but not too sure
Is there anything to consider when running transactions through the OLEDB Provider?
like don't run the SLX_DBIDS stuff or ?
Or can they just be ran and treated like writing any other SQL Code? |
|
|
|
Re: are there anythings to consider with transactions?
Posted: 26 Dec 08 11:17 PM
|
alright so I don't think it has to do with transactions....
Something is causing the SLX Provider to just lock up completely. Has anyone ever experienced this?
The first time I noticed this was a few months back when we were migrating all of our data from the old DB to the SLX Database
I was running a process that was inserting about 400,000 records into a Table we added through the DBManager
the query was fairly unexciting....
select * FROM OLDTABLE into C# DataTable
Loop through DataTable and for each record do
Get_slxidbs
insert into CustomTable
and that was it
no transactions or anything...
On our staging servers this ran w/o a problem.
But on our Production server every 40,000 or 50,000 records it would just lock up all requests on the server using the OLEDB Provider...
Effectively crashing all applications we had running on the server
The only way to recover it was to end task the SLXSystem.exe and restart the application...
I'm fairly sure it wasn't an SQL level thing because an SQL hangup throws a timeout exception..
I ran some tests with running queries against locked records and reliably it timesout after about 15 seconds...
But in this case every application running on our production server just freezes when it tries to execute a SQL Statement through the OLE DB PRovider
any suggestions?
|
|
|
|
Re: are there anythings to consider with transactions?
Posted: 27 Dec 08 12:22 PM
|
Check to see if your server has TCP/IP set as the default protocol. I have found that SQL 2005 defaults to Shared Memory and it will lock up the system under heavy loads. You should set the Default Protocol to TCP/IP for both the network and the client. You will probably need to restart the SQL Server after making the changes.
Hope this helps
Ray |
|
|
|
Re: are there anythings to consider with transactions?
Posted: 27 Dec 08 7:48 PM
|
I am not sure about the error you are having, but do have a comment about your 400,000 record migration. Your performance could have been improved significantly by retrieving all your IDs in one hit, rather than one at a time.
Here's an SSIS VB.NET function that I use:
Private Function SLXID(ByVal Table As String, ByVal Count As Long) As OleDbDataReader
'Returns an array of 'count' new SLX IDs for the specified table 'Table'
SLXID = New OleDbCommand("slx_dbids('" & Table & "', " & Count & ")", objSLXConn).ExecuteReader
End Function
It assumes that objSLXConn is an open SLX OLEDB connection and returns an OleDbDataReader containing 'Count' IDs.
Phil |
|
|
|
Re: are there anythings to consider with transactions?
Posted: 07 Jan 09 9:34 AM
|
Breakdown the problem/solution into smaller chunks.....process the chunk, and then release the memory.
400,000 records? Process in groups of 100, 500, 1000, 5000 AccountID's. A SQL Statement that returns 1 column width by 500 rows for example.
For each row, get the source data Row......80 columns wide, by 1 row.
For this row, insert 1 row into each table desired, release the memory after each record is stored.
Go process another batch of 500 or 1000 IDS.
Works every time.
|
|
|
|
Re: are there anythings to consider with transactions?
Posted: 26 Jan 09 10:55 AM
|
Don't generate the ids on the transaction because it will lock everything else that tries to generate ids (i.e. every Saleslogix process will grind to an halt fairly quickly). Also the other comment on generating the ids in advance is dead on. Get the source row count, generate the x number of ids, then start the transaction. If you can't generate the ids in advance then you can simply open a separate connection for that purpose.
Don't use transaction on 6.1 or 6.2.0 as they are too buggy. 6.2.3 and above are OK.
Large number of records is fine BUT be careful on the way you write the queries... If it is possible to process it in chunk it will be easier to avoid deadlocks. You have to be even more careful when using ADODB (instead of ADO.NET) because it can be a bit finicky (hard to explain... but it will sometimes open new connections seemingly for no reason which will mess up your locks... so I usually don't bother with transactions in ADODB, unless they are going to be very short-lived). |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|