|
I adjusted my code to use parameterized queries but have not gained a significant enough performance boost. After some performance tests I have found that my OleDbCommand.ExecuteNonQuery() takes an average of 0.004 seconds to execute (with the max occurance being 0.015 seconds). With my current mid-range test I do this for 145,160 records for a total of 9.85 minutes! After some more research it appears the general concensus is that ADO.NET is unable to do this type of operation any faster.
So, my new question is...
I have no choice in the requirements. I must take a DataTable and write it to disk as a dBASE IV table. Does anyone have any ideas on how to do this in the fastest possible way?
Mark Sanders
|
|
|
|
|
When I work with DBF files I always use microsoft's ODBC data provider classes, ODBC Adapters etc. So far it works fast and effective.
You can dowload it somewhere from the MSDN
Good luck.
"I only Play for Sport"
Lara H. Croft
modified 17-Oct-21 21:01pm.
|
|
|
|
|
Working with a table containing 2 million records I want to get the next SeqNum for each group and display an order field.
Example:
Table:
ID Field1 Field2 DateEntered SeqNum
1 tom a 12/12/00 1
2 tom b 1/1/01 2
3 jack d 2/2/01 1
4 tom d 1/2/02 null
5 me a 1/2/02 1
6 jack a 2/2/02 null
7 me d 3/3/03 null
8 me a 3/3/03 null
Desired Output:
ID Field1 Field2 DateEntered SeqNum Order
1 tom a 12/12/00 1 1
2 tom b 1/1/01 2 2
3 jack d 2/2/01 1 3
4 tom d 1/2/02 3 4
5 me a 1/2/02 1 5
6 jack a 2/2/02 2 6
7 me d 3/3/03 2 7
8 me a 3/3/03 3 8
Could someone point me in the right direction on how to replace the null values with the appropriate next SeqNum and output the Order fields.
Because of the size of the table using a cursor is out of the question. Anyone know of a good way this can be done.
Thanks,
Jason W.
|
|
|
|
|
Depends on the database you are using. If it is SQL Server or Sybase, consider creating an identity column for automatic numbering.
|
|
|
|
|
Jason Weibel wrote:
Could someone point me in the right direction on how to replace the null values with the appropriate next SeqNum and output the Order fields.
Because of the size of the table using a cursor is out of the question. Anyone know of a good way this can be done.
Jason, long time no see, not sure if you got this fixed or not but you should consider using a CASE statement in the stored procedure where you can replace a null value with an identity value for each record.
-Nick Parker
|
|
|
|
|
Man I thought you fell off the face of the planet. What’s up? I found a work around for the problem, it isn’t pretty but it works.
Jason W.
|
|
|
|
|
Jason Weibel wrote:
Man I thought you fell off the face of the planet. What’s up? I found a work around for the problem, it isn’t pretty but it works.
Did you get the email I sent you a few days ago? 
-Nick Parker
|
|
|
|
|
Hi all,
I am constructing a oledbconnection using the connection string which obtained through data link properties dialog.
After getting a connection string we can easily create oledbconnection irrespective of the database. My problem is I should know the oledbconnection is with an oracle database.
How can I know? The only way I know is checking the database provider in the connection string that it is from oracle. But what is the problem with this, this is always possible only if we know all the oracle oledb providers. That also will fail, if they change the version in future. Without using the provider in the connection string, is there any other way to determine whether the connection is with an oracle database?
Thanks.
|
|
|
|
|
Hi All,
I have the following scenario:
in a Bound windows form, I have a master-detail relationship, the first part of the form is the Master(represented by text and combo boxes) and retrives it's Data from a single SQL data table, The Details Section is represented by a DataGrid, and retrives its Data from Multi Tables Sql Query.
I wand to Add/ Insert/ Update and Delete from this Related sections at once, but still facing some problems affecting the Details Section, the modifications are done on the Master only.
The relation between them is build in the run time.
plz, if you have any Idea how to solve such a problem, that will be appreciated.
Thanx All.
I.M.A
|
|
|
|
|
HI,
I created tables in MS Access.. and am quite familiar in accessing the tables from the ASP page for my site. I then saw somethin like relationships.. and started playing around it. I got the followinf stuff....
Employee (table)
- EmpId
- DeptId
- Name
Department (table)
- DeptId
- DeptHead
- Description
The Dept head at the second table is linked with the EmpId in the first table by means of the relationships....
NOW, will it be possible for me to access the first table data, by querying the second table. (In access screen I was able to see the first table data in the second table by clicking + on the field which joins them)
Please help
I was born intelligent
Education ruined me!.
|
|
|
|
|
Hi,
> The Dept head at the second table is linked with the EmpId in the first table by means of the relationships....
From your table definitions, I think DeptId is a foreign key that links the Employee to a Department.
> NOW, will it be possible for me to access the first table data, by querying the second table.
It depends of what you want to do. What do you mean by "access the first table data" ? Do you want to know the employees for a specific departement ?
Just a DB design tip :
- don't use prefix on your field names : Employee.Id or Department.Id are ok, Employee.EmpId or Department.DeptId/DeptHead is a redondance
JM. Molina
Web: http://goa.ifrance.com
|
|
|
|
|
how to use ado.net access paradox database(tables)?
thanks.
|
|
|
|
|
i have a table in my Sql Server database which contains data in a hierarchical manner is as below
table-1
child_id parent_id
1 2
2 3
3 4
4 5
5 6
11 5
17 4
Now i want to retrive all the children,grand children,grand-grand children of parent_id=5.How i can achive it through SQL query .Please help me its very very urgent
Thank u
|
|
|
|
|
In Standard-SQL you got to program every level
i.e.
SELECT child_id, parent_id
FROM table
WHERE parent_id=5
SELECT child_id, parent_id
FROM table
WHERE parent_id IN (SELECT child_id FROM table WHERE parent_id=5)
etc.
you can find examples when looking for 'partlists'.
however, if you use Oracle, you can use the CONNECT BY clause:
SELECT child_id, parent_id
FROM table
CONNECT BY parent_id = child_id
|
|
|
|
|
You can use nested sets, I just submitted an article on it, have a look in the General/Database section, Look for "Improving Hierarchy Performance"
Cheers, James
James Simpson
Web Developer
Crown Management Systems
(http://www.crown.uk.com)
imebgo@hotmail.com
|
|
|
|
|
I am trying to install MySQL 4.0.12 on Windows 2000. I am getting very frustrated as I thought this would be simple. What a fool I was.  Can someone help me out?
The current problem is that the MySQL installation instructions for Windows do not mention anything about requiring Cygwin, but scripts like bin/mysqlinstall_db.sh aren't going to run unless Cygwin or something like it are present, no? So,
1. Does one need Cygwin on Windows 2000 to install MySQL, and if not, what does one do when one encounters instructions like "run mysqlinstall_db.sh" in the install notes, and
2. is there a set of instructions for installing MySQL on Windows that is better than the manual that comes with the MySQL dsitribution?
Andrew
|
|
|
|
|
|
Hi,
Simply download the Windows Binaries, install it and that's all. You don't need Cygwin unless you want to compile MySQL by yourself.
JM. Molina
Web: http://goa.ifrance.com
|
|
|
|
|
Could someone help me figure out what's wrong with this code? I'm using Microsoft SQL 7
SELECT DISTINCT UserID,
(
SELECT COUNT (UserID) AS test
FROM pollusers, polls
WHERE polls.UserID = pollusers.UserID
) AS CountID
FROM pollusers
This is the error I am getting in Cold Fusion: Invalid column name 'UserID'
|
|
|
|
|
It doesn't know which 'UserID' column to count, since the column exists in both of the tables you are joining. Try this instead
SELECT DISTINCT UserID,
(
SELECT COUNT (polls.UserID) AS test
FROM pollusers, polls
WHERE polls.UserID = pollusers.UserID
) AS CountID
FROM pollusers
Just guessing, though. What is the query supposed to accomplish?
Chris Meech
"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.
"Microsoft hasn't ever enforced its patents. Apparently they keep them for defensive reasons only. Or, they could be waiting 'til they have a critical mass of patents, enforce them all at once and win the game of Risk that they're playing with the world." Chris Sells Feb 18, 2003.
|
|
|
|
|
SQL is getting confused what column belongs to what table. Alias the table and it should work.
SELECT DISTINCT UserID,
(
SELECT COUNT (pu.UserID) AS test
FROM pollusers pu, polls po
WHERE pu.UserID = po.UserID
) AS CountID
FROM pollusers
If that still doesn't work, make sure that this works first:
select UserID from pollusers
select UserID from polls
|
|
|
|
|
I guess most of you know of NDoc, an exelent tool to create compiled HTML documentation files for your .NET applications.
Unfortuanly, NDoc have no feautures for ducmenting SQL Server DB's. The programming of a Data Access Component Layer would go must faster if you had a good documentation of all your SP's and tables.
There is however one excelent tool around, called Document!X, witch can document both .NET applications, COM objects, SQL Server DBs, ActiveX components and more. It simply digs it way trough the DB, gathering information on all SP's and tables, compiling a good HTML documentation file. http://www.innovasys.co.uk/products/documentx.asp is the URL to the product.
I was wondering if anyone knew of a free tool doing the exact same job, or if NDoc will support SQL DB's in the feauture.
Document!X is quite expensive, since you got most of the feautures you need for free in NDoc, and you only need it to ducment a DB...
|
|
|
|
|
|
I have run a few tests and am now convinced that there must be a problem with connection pooling, or connections not being freed up correctly by .NET
My test is as follows. I have an XML Web Service running on an IIS site running on .NET 1.0 with all service packs applied with the following site configuration:
Anonymous Access: Yes
User name: domain\xxxxx <-- this username has access to the SQL Server
Allow IIS to control password: Yes
Basic Authentication: No
Integrated Windows authentication: Yes
The test method I am calling is written in VB.NET (code follows), when I pass in '50', the method creates 50 connections, pauses for 20 seconds to hold them open, and then they are closed and disposed - this works fine. The problem happens when I pass in '150'. The following exception is thrown;
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. at System.Data.SqlClient.SqlConnection.Open() at MyService.DataService.AuthenticateUser(String DomainUsername) in \\servername\CREQSERVICE\DataService.asmx.vb:line 172 --- End of inner exception stack trace ---
But even worse, the connections never seem to be released and the server becomes unstable; further connections to IIS time out. If I stop/start the web service it doesn't clear the connection pool. I have tested this on three different test servers, and get the same problem.
Any help with this would be appreciated, I'm trying to get some code into production, and this problem has delayed me for a week already. Does anyone know if there is a known bug with connection pools?
Code used for testing:
<WebMethod()> Public Function CreateTestConnections(ByVal NumberOfConnections As Integer) As String
'This is for testing only!, remove from production code
Dim conSQL As Array = Array.CreateInstance(GetType(SqlConnection), NumberOfConnections)
Dim t As Long
For t = 0 To NumberOfConnections - 1
Try
conSQL(t) = New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))
conSQL(t).Open()
Catch e As Exception
End Try
Next t
'pause for 20 seconds
Dim Start, Finish, TotalTime As Double
Start = Timer
Finish = Start + 20.0
Do While Timer < Finish
' Do other processing while waiting for 20 seconds to elapse.
Loop
TotalTime = Timer - Start ' Calculate total time.
For t = 0 To NumberOfConnections - 1
conSQL(t).Close()
conSQL(t).Dispose()
Next t
Return "Finished"
End Function
www.silveronion.com[^]
|
|
|
|
|
I am doing a project in SQLJ and would like to know if the connection to the Database(Oracle) exists until,I explicitly
call the Oracle.close() function or will it be closed automatically after fetching the resultset?
Preferably I want to keep the connection alive as long as I want to and close it only when I want to.(i,e when I close the window of a GUI, the connection to the Oracle database should close)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
