Speed SQLExpressvs Access2003 Via Oledb??? Advantages???
Mar 19, 2008
I am trying to evaluate the pros and cons of using SQL Server over oledb connection to an access database.
So far
Pros Cons
Access: Doesnt need to be installed by user. 2GB Data limit
SQLExpress:
Not sure yet - more than 2gb data User has to install SQL Server compact.
Is there anything iv missed?
Iv run a quick test to decide on speed my results seem to suggest that oledb is faster at writing data, and SQL Server is faster at reading.
The test program clears the database:
and times how long it takes to insert 10000 records, the read the 10000 records back for OLEDB, then SQL Express.
Code Snippet
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Timers;
namespace ConsoleApplication1
{
class Program
{
static int a = 0;
static int b = 0;
static void Main(string[] args)
{
Timer tmr0 = new Timer(1);
Timer tmr1 = new Timer(1);
tmr1.Elapsed += new ElapsedEventHandler(tmr1_Elapsed);
OleDbConnection oledbcon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database1.mdb");
oledbcon.Open();
OleDbCommand oledbcmd = new OleDbCommand("INSERT INTO Table1 (Can) VALUES ('1')",oledbcon);
oledbcmd.CommandText = "DELETE * FROM Table1";
oledbcmd.ExecuteNonQuery();
tmr0.Elapsed += new ElapsedEventHandler(tmr0_Elapsed);
tmr0.Enabled = true;
for (int i = 0; i < 10000; i++)
{
oledbcmd.CommandText = "INSERT INTO Table1 (Can) VALUES ('" + i.ToString() + "')";
oledbcmd.ExecuteNonQuery();
}
int aa = a;
a = 0;
oledbcmd.CommandText = "SELECT * FROM Table1";
OleDbDataReader oledbreader;
oledbreader = oledbcmd.ExecuteReader();
while (oledbreader.Read())
{
oledbreader.GetInt32(0);
}
tmr0.Enabled = false;
SqlConnection sqlcon = new SqlConnection("Data Source=localhost\SQLEXPRESS;Initial Catalog=;Integrated Security = SSPI");
sqlcon.Open();
SqlCommand sqlcmd = new SqlCommand("CREATE DATABASE Test",sqlcon);
try
{
sqlcmd.ExecuteNonQuery();
}
catch { }
sqlcmd.CommandText = "CREATE TABLE Table1 (num INT)";
try { sqlcmd.ExecuteNonQuery(); }
catch { }
sqlcmd.CommandText = "DELETE FROM Table1";
sqlcmd.ExecuteNonQuery();
tmr1.Enabled = true;
for (int i = 0; i < 10000; i++)
{
sqlcmd.CommandText = "INSERT INTO Table1 (num) VALUES ('" + i.ToString() + "')";
sqlcmd.ExecuteNonQuery();
}
int bb = b;
b = 0;
sqlcmd.CommandText = "SELECT * FROM Table1";
SqlDataReader sqlreader = sqlcmd.ExecuteReader();
while (sqlreader.Read())
{
sqlreader.GetInt32(0);
}
tmr1.Enabled = false;
Console.WriteLine("OLEDB");
Console.WriteLine("Inserted 10000 records in "+aa.ToString()+"ms");
Console.WriteLine("Read 10000 records in " + a.ToString() + "ms");
Console.WriteLine("TOTAL: " + ((a + aa).ToString()) + "ms");
Console.WriteLine("SQL SERVER");
Console.WriteLine("Inserted 10000 records in " + bb.ToString() + "ms");
Console.WriteLine("Read 10000 records in " + b.ToString() + "ms");
Console.WriteLine("TOTAL: " + ((b + bb).ToString()) + "ms");
Console.ReadKey();
}
static void tmr1_Elapsed(object sender, ElapsedEventArgs e)
{
b++;
}
static void tmr0_Elapsed(object sender, ElapsedEventArgs e)
{
a++;
}
}
}
For the access database create a access2003 mdb file with a table "Table1" and a colomn of type integer called "Can";
save it as C:database1.mdb
Then compile and run above.
My results wereslow machine)
OLEDB
Inserted 10000 records in 1512ms
Read 10000 records in 6ms
TOTAL: 1518ms
SQL SERVER
Inserted 10000 records in 1853ms
Read 10000 records in 1ms
TOTAL: 1854ms
Does this
View 2 Replies
ADVERTISEMENT
Jun 13, 2006
Hi,
Just wondering if some one can help me out here I know that we can work with access2003 as a front end and SQL2000 as a back end. What I am trying to do now is working with SQL2005 as a back end I was wondering if it can be done ( I can access it but having errors creating tables etc).
Thanks
Sorry I was not sure where exactly to post this topic as it relates to both dbs so I have posted this same thread in Access as well.
View 2 Replies
View Related
Oct 18, 2007
Hi,
I have several data bases on a server (SQL Server 2000 only, no web server installed) and lately, as the company keeps gowing, my users complain saying the server gets slow, (this dbs are well designed and recieve optimizations and integrity checks, etc) because of this, Im thinking about getting a new server to repleace my old ProLiant ML 330 which was bought 4 years ago but Im concerned about what server arquitecture or characteristic can help me best to improve response performance, is it HD speed? Processor speed? or more Ram? I want to make a good decision, so I´d really appreciate your help...
Thanks, Luis Luevano
View 1 Replies
View Related
Dec 27, 2006
Hi ,
I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?
Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access.If there is no change in schema between source and destination databases (more of upsizing) then the process is pretty straight forward , The constraint here is that the data needs to be migrated to a new schema where the column names and number of columns can vary between the source table and destination table.. I just need to migrate data only and no other objects.
Need Help!
Thanks
Mahesh
View 1 Replies
View Related
Feb 20, 2008
I have created an Access2003 project (existing data) that links to external data. First I connected to a SQL Server 2000 database. Success. Then I tried to set up a Transact SQL data connection to a legacy MDW-secured Access97 database. (A third-party VB6 application goes against it, and we don't have the source code, so we cannot upgrade it.)
The Transact SQL link tests OK but I cannot select any of the tables or queries from the list presented. However, with the same credentials, I can use these same objects in Excel 2003.
When setting up the link in Access2003, I specify JET 4.0 OLE DB Provider, I enter the MDW file on the All tab, a username and a password on the Connection tab where I browse to the MDB file, and specify Shared Deny None on the Advanced tab. When I test the connection, it tests OK ("Test connection succeeded"). Yet on the "Select the Database and Table/Cube which contains the data you want" dialog, "(Default)" appears in the grayed-out dropdown. Then, beneath that dropdown, there is a grid with Name and Description columns. The grid contains query names but the grid is not enabled. The list of queries is this table is grayed out. Neither of the scrollbars works.
BUT... if I use the SAME username and password in Excel2003, and specify the same MDW, there is no problem working with these same database objects in the legacy Access97 database. WHAT IS DIFFERENT ABOUT THE WIZARD IN EXCEL THAT ALLOWS IT TO SUCCEED AND THE WIZARD IN ACCESS THAT CAUSES IT TO FAIL HERE? In Excel, the list of available providers says Microsoft Access Driver, not JET 4.0 OLE DB Provider.
Thanks
TR
View 1 Replies
View Related
Oct 10, 2006
Hi all,
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
(Microsoft.DataTransformationServices.VsIntegration)
View 5 Replies
View Related
May 3, 2002
Hi,
I was looking for some information / papers giving the advantages of Sql 2k over sql7.
Thanks!
View 1 Replies
View Related
Apr 20, 2004
hi ! my boss is thinking of redoing our accounting system which is currently running on FOXPRO - we are planning on a VB/SQL SERVER platform.but he isn't convinced that the benefits of SQL server outweigh those offered by FOXPRO especially since everyone in the office is very comfortable with Foxpro.Can anyone give me some solid advantages of SQL SErver or any other RDBMS over Foxpro ?
View 14 Replies
View Related
Oct 4, 2006
Hi,
If I have an application that has at most 20 users with an average of 3-4 concurrent requests to the server and the databases size is 1 gig probably to grow to at most 1.5 gigs in the next 5 years why would I choose Express over MSDE?
MSDE can take advantage of more than 1 gig of memory and can use 2 CPUs. I really don't see any benefit whatsoever in my case to go to SQL Express, in fact all I see is drawbacks.
I hear about upgrading all over the place but I just don't see any good reason in my situation. Am I missing something here?
View 7 Replies
View Related
Jan 3, 2007
This may be too general a question but I'm going to ask it anyway.
I'm moving data from a source DB (say A) to a target DB (say B). On A I need to join 3 tables and, after some lookups etc., I need to populate several tables in B. Inserting into B's tables involves sequential operations because in many cases I have to get back the value of an Identity column to use as in input value in a another table 'downstream'. Additionally, the tables in B are populated as a group i.e. if the insert on any one fails the entire group's insertion needs to be rolled back.
I set up a set of stored procedures to do this. The master Stored Proc opens a read-only cursor and for each row of the cursor executes the other SPs in proper sequence. Some of the SPs are 'enclosed' within a transaction to enable a Rollback on the group.
My major concern with this approach was the 'known' inefficiency of the cursor and the huge memory requirement it's use would entail (the cursor would pull about 15 million rows).
So I began looking into SSIS thinking it would be able manage the system resources aspect effectively and offer better performance overall. I've realized, however, that even in SSIS I would essentially need to first pull the 14 mil rows into a memory-resident object (or a temp table - whose benefit I'm not convinced of or haven't fully understood) before looping through each row to perform the data inserts into B.
So, is there any real advantage to this over the first approach ? Perhaps I haven't looked deep enough or wide enough. Any constructive suggestions / feedback would be highly appreciated.
Thanks.
View 5 Replies
View Related
Jun 22, 2007
What is the advantages/disadvantages of using Database Diagram and link all the tables in MS SQL Server Management Studio versus letting the application check and link the different tables at run time? Currently, I do not have all my tables linked in a Database Diagram. I do everything at run time in my application code behind. What are the best practices? Which is easier or perhaps more secure?
View 4 Replies
View Related
Jun 20, 2008
Hi to all, Can anybody tell me what are the main advantage's of sql server 2005 ?
View 1 Replies
View Related
Aug 3, 2004
Hi,
Generally I write all my SQL in Stored Procedures instead of using adhoc queries. But I dont feel good about stored procedures when I come across situations like this.
Lets say that I have a stored procedure something like this
CREATE PROCEDURE dbo.proc_MYSP
@CaseID char(10)
AS
SELECT * FROM TABLE1WHERE CASEID = @CASEID
Suppose in future if the field CASEID is changed to char(20) then I need to change the declaration of CaseID in all my stored procedures that take CaseID as input parameter. If I write adhoc queries then I need not worry about this. Is there any effective solution for a situation like this.
Thank you.
View 5 Replies
View Related
Oct 9, 2007
hi guys,
im new to sql server...
i am currently studying sql server 2000
i know this subject has been asked before...
i would just like to ask if you know some related articles regarding advantages of using sqlserver over vfp dbf/dbc?
i am going to discuss it to my class.
thanks very much!
any help is very much appreciated!
Joel
View 4 Replies
View Related
Feb 13, 2008
hi,
Can any body help me in knowing what are the advantages and disadvantages of all the three replication types?
View 1 Replies
View Related
Dec 18, 2006
I have a content site where everything is currently in one SQL ServerDB. As I add features to the site, for example message boards andblogging, does it make sense to put those features in a separatedatabase? What would I lose and gain in doing so? Thanks so much.Erik
View 3 Replies
View Related
Mar 11, 2008
Can I get answer of this ? with some of examples if it is possible.
View 2 Replies
View Related
Jul 12, 2000
Hi, I would appreicate your opinion/ feed back about Being consultant vs Being employee.
What are the advantages/disadvantages.
Does consultant makes more money than an employee and why so
Thanks for your response
Ali
View 2 Replies
View Related
Aug 5, 2005
Are they're any real disadvantages or advantages in having 1 massive disk partioned to create 2 logical drives (not including the C drive) and separating the SQL Database File & Transaction Log so that it doesnt reside on the same logical drive?
After all, it is still a single disk.
thanks.
View 3 Replies
View Related
Nov 6, 1999
Just wondering - on a straight RAID 5 system is there any advantage, performance or otherwise, to splitting a SQL 6.5 database over multiple database devices? Or for simplicity's sake am I just further off creating a single device and manually extending it as the database grows?
Thanks
RM
View 1 Replies
View Related
Jun 12, 2008
Dear All,
what are the advantages of changing compatibility level from 80 to 90? are there any disadvantages doing this on production machine? will it take any downtime?
i've searched the google but i didnt get the correct info.
Arnav
Even you learn 1%, Learn it with 100% confidence.
View 2 Replies
View Related
Nov 23, 2007
Please reply me as soon as possible.
View 4 Replies
View Related
Sep 27, 2007
Hello,
I am working on the Reporting Services 2005, I don' know anything about Business Object.
Please let me know what are the advantages of Reporting Services 2005 over the Business Object.
Any specific link will be useful for me.
Thanks in advance.
Bye.
View 5 Replies
View Related
May 14, 2015
Give the Advantages Over SQL SERVER Always on over Clustering....
View 4 Replies
View Related
Jan 19, 2007
Dear Sirs and Madams,
it's quite hard to find informations about OLAP mining I think. So I have some questions to you:
What advantages do I have by using Olap Mining instead of "normal" mining (relational databases)?
Is it just faster or are there other advantages?
What are the disadvantages? The data can be overaggregated (no detailled results), can not? Are there other disadvantages?
What problem do I have to face?
Whats going on with empty cells?
Thank you very much in advance and have a nice weekend.
Thorsten
View 1 Replies
View Related
Jul 3, 2006
Hi everyone,
When we create a clustered index firstly, and then is it advantageous to create another index which is nonclustered ??
In my opinion, yes it is. Because, since we use clustered index first, our rows are sorted and so while using nonclustered index on this data file, finding adress of the record on this sorted data is really easier than finding adress of the record on unsorted data, is not it ??
Thanks
View 4 Replies
View Related
Sep 19, 2006
Andy writes "I have data with more than 4 milions. How to speed up query it ?"
View 3 Replies
View Related
Mar 9, 2007
hello,
i need some opinion on how to sum up or group by more than 2k records faster.. eg, how do i optimize this?
SELECT DISTINCT r.ClientID,c.ClientName, r.ItemID, r.StockID,r.StockName, r.ExpectedQty,r.QCQty,r.AVAQty,r.PNDQty as pnd, r.VMIQCQty,r.VMIAVAQty,r.VMIPNDQty as vmipnd,
(Select isnull( SUM(d.HoldQty) ,0) FROM tblItemdetail d WHERE d.itemid=r.itemid AND d.ConsignorID=@ClientID AND d.Ownerstatus='VMI') AS VMIPNDQty,
(Select isnull( SUM(d.HoldQty) ,0) FROM tblItemdetail d WHERE d.itemid=r.itemid AND d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='VMI') AS PNDQty,
(Select isnull(SUM(d.OriginQty - d.PickQty -d.HoldQty -d.qcqty),0) FROM tblItemDetail d WHERE d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='OWN') AS StockAtCustAVAQty,
(Select isnull(SUM(d.HoldQty),0) FROM tblItemDetail d WHERE d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='OWN') AS StockAtCustPNDQty,
(Select isnull(SUM(d.qcqty),0) FROM tblItemDetail d WHERE d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='OWN') AS StockAtCustQCQty
FROM tblItemCrossRef r
INNER JOIN tblClient c ON c.ClientID=r.ClientID
INNER JOIN tblItemClients i on i.Supplier=r.ClientID
WHERE r.ClientID=@ClientID AND r.StockID LIKE @StockID+'%'
~~~Focus on problem, not solution~~~
View 5 Replies
View Related
Jun 22, 2006
Hello all-Given the following UDF, in sql 2000 can it be sped up, complied oranything of the like. A query returning 300,000 + rows times out whenran through the udf, inline case statements returns the rows in 5seconds.Thanks!JeffCREATE FUNCTION dbo.TimeFormat(@input datetime,@groupformatvarchar(20) --DAY, WEEK, MONTH)RETURNS datetimeASBEGINdeclare @dtvar as datetimeif @groupformat = 'DAY'set @dtvar = CAST(CONVERT(char(10), @input, 101) AS datetime)else if @groupformat = 'WEEK'set @dtvar = CAST(DATEADD([DAY], 1 - DATEPART(dw, CONVERT(char(10),@input, 101)), CONVERT(char(10), @input, 101)) AS datetime)else if @groupformat = 'MONTH'set @dtvar = CAST(CONVERT(CHAR(6), @input, 112) + '01' AS datetime)return @dtvarEND
View 9 Replies
View Related
Jul 20, 2005
This is x-posted in:alt.php.sqlcomp.databases.ms-sqlservermicrosoft.public.sqlserver.programmingI have events that occur during the day. I want to be able to search thoseby a form with checkboxes (multiple select).Let's say for instance an event is happening from 3-10pm. When someonesearches for 4-6 (checkbox option) it needs to show up.I don't need code so much as I just need theory. My theory that I coded outand worked, just a missight in theory is as follows. I did a BETWEEN callthat pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6.As you can see. The event spans that time, but does not start or stopbetween 4 and 6, thus was not pulled. Ooops.So if someone call tell me of another function or perhaps just a better wayto use BETWEEN that would be great. I don't think that code is necessary atthis juncture, so save the 'Please post code' post :) Thanks.
View 12 Replies
View Related
Jul 12, 2006
Hello,
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?
View 1 Replies
View Related
Jan 17, 2007
Hi,
Can anyone tell me a way to speed up these querys?
//This is selecting a number of records (sent by user) from a table and randomizing those
tempSQL.Text = "select top " + amount.Text + " number from [" + src.Text + "] Where pull='N' order by newID()";
SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
conn2.Open();
SqlCommand cmd3 = new SqlCommand(tempSQL.Text, conn2);
cmd3.CommandTimeout = 1000;
SqlDataReader dr = cmd3.ExecuteReader();
//Then I open a data reader that uses the records
SqlConnection conn2a = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
conn2a.Open();
while (dr.Read())
{
//the records are then placed 1 by one into a temp table
string fillresultID = "Insert into [" + src.Text + "_Additional_Temp] (number) Values('" + dr["number"] + "')";
SqlCommand cmd4 = new SqlCommand(fillresultID, conn2a);
cmd4.CommandTimeout = 0;
cmd4.ExecuteNonQuery();
//then the original table that held the numbers is marked as used(again one by one)
string update = "Update [" + src.Text + "] set pull='Y' where number='" + dr["number"] + "'";
SqlCommand cmd5 = new SqlCommand(update, conn2a);
cmd5.CommandTimeout = 0;
cmd5.ExecuteNonQuery();
}
dr.Close();
conn2.Close();
conn2a.Close();
Thanks,
Doug
View 5 Replies
View Related
Apr 22, 2008
Hi, how can i speed up this query, it seems to be taking a very long time to bring back the reults;
--This stored procedure retrieves access rights for usersCREATE PROCEDURE wc_User_Access_Right_List
ASSELECT dbo.tblRep.Rep_ID, RTRIM(dbo.tblRep.Rep_Forename) + ' ' + RTRIM(dbo.tblRep.Rep_Surname) AS User_Full_Name, dbo.tblAccessRight.Access_Right, dbo.tblAccessRight.Access_Right_IDFROM dbo.tblRep LEFT OUTER JOIN dbo.tblAccessRight ON dbo.tblRep.Access_Right_ID = dbo.tblAccessRight.Access_Right_ID ORDER BY User_Full_Name
--Make sure this has saved, if not return 10 as this is unexpected error
IF @@rowcount = 0 return 10
DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO
View 21 Replies
View Related