|
Aloha,
We need to make a small Win app. that work together with an Access database. Everything works, but it's awfully slow
It takes around 60 milliseconds for our code to perform a retrieve a scalar value from the database. This is terrible, since we have quite a lot of queries to the database.
Our code is quite simple:
DbConnection dbConnection = dbProviderFactory.CreateConnection();<br />
dbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb";<br />
<br />
dbConnection.Open();<br />
DbCommand dbCommand = dbProviderFactory.CreateCommand();<br />
dbCommand.Connection = dbConnection;<br />
<br />
dbCommand.CommandType = CommandType.Text;<br />
dbCommand.CommandText = sql;<br />
<br />
object obj = dbCommand.ExecuteScalar();<br />
<br />
dbCommand.Connection.Close();<br />
<br />
return obj;
Is it supposed to be so slow or are we doing something terribly wrong? Our SQL is just a simple select from two tables.
SELECT [text] FROM Question, UserText WHERE Question.QuestionTitle = UserText.TextId " +<br />
"AND QuestionId = " + controlId.ToString() + " AND LanguageId = " + languageId;
Thank you,
|
|
|
|
|
MS Access performance sucks
If performance is a concern then you may want to consider SQL Express ...
From a quick glance, your query looks okay.
|
|
|
|
|
We all now that MS Access performance sucks... it's just a lot worse than I thought it would be
|
|
|
|
|
phimix wrote: We all now that MS Access performance sucks...
Yep.
phimix wrote: it's just a lot worse than I thought it would be
I think it is a Your mileage may vary type of thing. I know a guy who is using an Access database that has 50-60 tables with 160,000+ records in each table, with 6-10 users and not having any trouble with it.
I'd look at the queries and general structure of the database and look for any small bottlenecks. Your performance issue could be something very small and subtle that is compounding into a performance issue
|
|
|
|
|
phimix wrote: Is it supposed to be so slow or are we doing something terribly wrong? Our SQL is just a simple select from two tables.
I have had pretty good luck with Access in small environments. How long does this query take to run from the Access database as a query?
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
what is primary key, candidate key, composite key and unique key? How many primary keys can be there in a table? composite key can act as a foreign key?
Mohinder Singh
|
|
|
|
|
Many of the database makers have tutorials available on their web sites.
An example database tutorial visit ...
http://www.geekgirls.com/menu_databases.htm[^]
Also, make a search here at Code Project, there are many database tutorials at various levels
modified 1-Aug-19 21:02pm.
|
|
|
|
|
|
That depends how you mean. Do you want to call a proc which creates another proc ? You can do that, but why ?
Christian Graus - C++ MVP
|
|
|
|
|
|
I suspect you will have to build the string for the new proc then execute it.
Something like this<br />
<br />
DECLARE @NewProc varchar(1000)<br />
<br />
SET @NewProc = 'Create Proc MyNewProc AS select * from Address go'<br />
<br />
exec (@NewProc)<br />
<br />
<br />
exec ('MyNewProc')
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Then why on earth are you asking them here ?
Christian Graus - C++ MVP
|
|
|
|
|
Christian Graus wrote: Then why on earth are you asking them here ?
Doesn't sound like interview went too well for the OP? Or perhaps the OP is looking for assurance that the questions were answered correctly
|
|
|
|
|
yes
yes
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
I want to know examples of shared,update,exclusive locks in sql server 2000
|
|
|
|
|
Google broken where you live ?
here[^]
Christian Graus - C++ MVP
|
|
|
|
|
Hi
I am facing a problem. I want to upload data from my local database to internet database(like exporting data) using an asp.net web page.I hve to delete all the data in internet database. That i ve done correctly. Now i want to know wether there is any method for exporting data from local database to internet database . When a asp.net button is clicked the local database's values must be loaded ito internet database . pls help me .
|
|
|
|
|
Export your DB table to CSV (Comma Seperated Files)
Upload these via an asp/x upload page.
Create SQL Server DTS that imports these into your internet db
Use another/same asp/x to run the DTS via a SQL script.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Sir,
Thanks for helping me
I am new to asp.net so its very difficult for me to understand. can you pls explain this.Wht is csv,
dts.
pls help me its very important
|
|
|
|
|
In a Employee table how can i retrieve a 3 maximum salary from that table
Please guide me
-
|
|
|
|
|
The Top keyword will allow you to select 3. The Order By keyword will allow you to order the items by salary, so you select the three highest.
Really, any site on SQL will tell you that ( or any book ) and I can't give more precise info without knowing your table schema.
Christian Graus - C++ MVP
|
|
|
|
|
select top 3 * from Employee order by salary desc
--replace column salar with your real column name, same do with table name
"My advice to you is to get married. If you find a good wife, you will be happy; if not, you will become a philosopher." Socrates
|
|
|
|
|
Hi, I would like to know how do i select the last inserted row in a table
Thank you
La Light
|
|
|
|
|
Do you need to whole row or just the ID? You could try something like this for the whole row:
SELECT * FROM tblSomething WHERE tblID = IDENT_CURRENT('tblSomething')
Pablo
Sometimes I think there's no reason to get out of bed . . . then I feel wet, and I realize there is.
|
|
|
|
|
but when your ids are uniqueidentifiers you do have a the current id do you? and i'm not necersary selecting in the at the same time when the rows are selected.
La Light
|
|
|
|