INSERT performanse (ODBC to MS SQL) - Database Discussion Boards

kallileo14-May-07 11:36

14-May-07 11:36

Yes you are absolutely right....

but this query doesn't work.

SELECT [rawvalue]
FROM [datastore]
WHERE timestamp=((SELECT MAX(timestamp) FROM [datastore]) WHERE name='s1')
AND name='s1'

*******
Error in FROM clause: near 'WHERE'.
Unable to parse query text.
******

Re: Problem with query and datareader

Colin Angus Mackay14-May-07 11:40

Colin Angus Mackay

14-May-07 11:40

Too many brackets, remove one before the SELECT and the one after [datastore]

Upcoming events:
* Glasgow: SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services...

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Re: Problem with query and datareader

kallileo14-May-07 12:22

kallileo

14-May-07 12:22

Its ok now thank you very much!

Rerun access formwizard [modified]

Dan Neely14-May-07 4:25

Dan Neely

14-May-07 4:25

Is there a way to take an existing form and rerun the wizard if the data it's pulling needs to be tweaked?

-- modified at 11:04 Monday 14th May, 2007
Fixed subject

--
You have to explain to them [VB coders] what you mean by "typed". their first response is likely to be something like, "Of course my code is typed. Do you think i magically project it onto the screen with the power of my mind?" --- John Simmons / outlaw programmer

Re: Rerun access formwizard

Dave Kreskowiak14-May-07 12:36

Dave Kreskowiak

14-May-07 12:36

I haven't seen a way to rerun the wizard without scrapping the form and starting over.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Readonly Forms in Acess

Dan Neely14-May-07 4:24

Dan Neely

14-May-07 4:24

Is there a way to set a form to only display data and not allow the user to change it?

Re: Readonly Forms in Acess

Paul Conrad14-May-07 7:26

Paul Conrad

14-May-07 7:26

Set all of the data entry fields to read-only by selecting all of the controls and setting the Locked property to Yes

INSERT performanse (ODBC to MS SQL)

_TLS14-May-07 4:15

_TLS

14-May-07 4:15

Hello everyone!

Right now I've created a small Windows service that parses binary data files and stores information from there to a MS SQL Server database. Right now each file takes about 15 mins to parse, and that is about 3500 - 4000 INSERT statements.

I'm worried that this time gets bigger as the database grows. Okay, I don't have a production server and it all runs on a standard desktop-like hardware, but still 15 mins is way too much for 3000 operations.

Right now it uses CRecordset MFC classes, and this type of cycle running:

<br />
CRecordset db;<br />
db.Open()<br />
while (run)<br />
{<br />
  db.AddNew();<br />
  ...<br />
  db.Update();<br />
}<br />
db.close()<br />

Are there any ways to boost all this up? What I have in mind is switching to another technology (OLE DB or even a 3d-party classes like SQLApi), or implementing bulk inserts, but haven't yet found any good examples/articles.

I appreciate your help (or links) greatly!

--
Take care!

Re: INSERT performanse (ODBC to MS SQL)

Dave Kreskowiak14-May-07 5:13

Dave Kreskowiak

14-May-07 5:13

INSERT performance, in the long run, is going to depend on your database index structure. The more indexes you have on the fields of a particular table, the slower non-SELECT operations become because of the time required to maintain those indexes on every change in the database. Indexes speed up SELECT operations, but slow down everything else. You have to find a balance between the two that is acceptable to you.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: INSERT performanse (ODBC to MS SQL)

andyharman14-May-07 6:01

andyharman

14-May-07 6:01

Just over 3 inserts per second seems to be very slow indeed.
You should get better performance from using bcp.exe or DTS to do bulk inserts.

Databinding using the BindingNavigator Control

Quecumber25614-May-07 3:20

Quecumber256

14-May-07 3:20

Hi Everyone:
I have designed a Windows data entry form that contains the following standard controls: 5 – labels, 1 – List box, and 4 –text boxes. The purpose of this form is to enter data into a single SQL server 2005 database table.

Visual Studio 2005 has a nifty control called the BindingNavigator. I would like to use this control so the user can add, edit, delete or just plain navigate though the data stored in the database table.

Does anyone know where I can find some step by step instructions on how to do this using VB.NET code? I tried using the connection wizard and couldn’t get it to work like I wanted it too.

Thanks in advance,

Quecumber256

Re: Databinding using the BindingNavigator Control

Muhadeeb9914-May-07 3:37

Muhadeeb99

14-May-07 3:37

I'm sure that Microsoft has some tutorials on there web site pertaining to SQL Server express ,also too, by Bob Tabor at learnvisualstudio.net
http://www.learnvisualstudio.net/content/series/getting_started_with_sql_server_2005_express_edition.aspx[^]

All things being equal, tommorrow will never equal today

MSDE File size limits

Not Active14-May-07 2:23

Not Active

14-May-07 2:23

MSDE has a 2GB database limit, what I need to know is does this inlcude the log file size also or is it just the maximum size of the data file? I can't find an documentation about it.

only two letters away from being an asset

Re: MSDE File size limits

kubben14-May-07 4:04

kubben

14-May-07 4:04

Just a question, why aren't you using sql express? It has a 4GB database limit and allows you to fully use 1 CPU. To answer your question, the limit is per database. Since the log file is part of the database I believe it is included, but I could be wrong.

Here is a link comparing msde and sql express
http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx[^]

Ben

Re: MSDE File size limits

Not Active14-May-07 4:19

Not Active

14-May-07 4:19

MSDE was installed previously. I needed to find the answer to convince everyone that SQL Express was the better choice and fight the "it's not broken, don't fix it" arguments. They have been experiencing issues retoring a database that is well over the 2 GB limit, with log file.

only two letters away from being an asset

Re: MSDE File size limits

kubben14-May-07 4:47

kubben

14-May-07 4:47

Well, I don't know if this totally works, but you could try removing the log and then seeing if you could restore without the log. I haven't actually tried it. If you can restore without the log then it would seem that the database limit includes the log file.

Ben

Re: MSDE File size limits

Dave Kreskowiak14-May-07 5:08

Dave Kreskowiak

14-May-07 5:08

Mark Nischalke wrote:
They have been experiencing issues retoring a database that is well over the 2 GB limit, with log file.

Well, I guess it's not "not broken" then, is it?! Big Grin | :-D

On top of that, MSDE isn't going to be supported on any O/S past Windows XP.

MSDE had a 2GB data file limit. This includes the MDF and NDF files, NOT the log files.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

Re: MSDE File size limits

Not Active14-May-07 5:27

Not Active

14-May-07 5:27

Thanks. It makes sense, but without any clear documentation that I could find I was unsure.

only two letters away from being an asset

Re: MSDE File size limits

Dave Kreskowiak14-May-07 12:30

Dave Kreskowiak

14-May-07 12:30

Yeah, I had to dig for a while to find it. It just didn't feel right saying that it included the log file when it's possible for a log file to be huge compared to the size of the data. So, I had to hunt for it. Big Grin | :-D

A guide to posting questions on CodeProject[^]

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

dataset not populated whereas Query Analyzer generates rows using the same query [modified]

fulbright14-May-07 1:15

fulbright

14-May-07 1:15

Hi ,

running the query ,say, strQuery , in a query analyzer generates results returning rows ...but folowing code does'nt make it the same ...

strQuery = " some query which works in query analyzer";
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAadapter(strQuery,connetion);
ds.tables.add("tablename");
adapter.fill(ds,"tablename"); //it takes 10 seconds at min
....
...
...
int nRows = ds.Tables[0].Rows.Count; // nRows is always 0 showing no record

any help ,,,,, Smile | :)

-- modified at 7:42 Monday 14th May, 2007

Madni

Re: dataset not populated whereas Query Analyzer generates rows using the same query

Giorgi Dalakishvili14-May-07 1:21

Giorgi Dalakishvili

14-May-07 1:21

You need to set selectcommand for SqlDataAadapter

Re: dataset not populated whereas Query Analyzer generates rows using the same query

fulbright14-May-07 1:35

fulbright

14-May-07 1:35

I set this property with the SqlCommand instance ... didnt work either

Madni

Re: dataset not populated whereas Query Analyzer generates rows using the same query

Giorgi Dalakishvili14-May-07 1:38

Giorgi Dalakishvili

14-May-07 1:38

Can you post the code snippet how you do it?

Re: dataset not populated whereas Query Analyzer generates rows using the same query

fulbright14-May-07 1:45

fulbright

14-May-07 1:45

this is almost the same as posted ,.... infact my development machine doesnt support the internet facility ... it will be hard for me to do that ... should i have to add the columns as well in datatable before adding it to dataset ,,,.... ???

Madni

Re: dataset not populated whereas Query Analyzer generates rows using the same query

sam#14-May-07 1:37

sam#

14-May-07 1:37

fulbright wrote:
SqlDataAdapter adapter = new SqlDataAadapter(ds,connetion);

replace above line with

SqlDataAdapter adapter = new SqlDataAadapter(strQuery,connetion);

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.