|
Hi
anyone care to point me at a SQL optimization 101 type FAQ etc eg dos and don't of tables/views/queries server config etc
Thanks
Shaun
|
|
|
|
|
Well, a good site on SQL Server optimization is http://www.sql-server-performance.com/[^]. Maybe it has too much information for you to read, so I recommend you to read some introduction first.
[shameless plug]
For the DO's and DON'Ts part, I can recommend you my article, SQL Server Do's and Dont's[^]
[/shameless plug]
I see dumb people
|
|
|
|
|
thanks that site is a great help - confirmed somethings I'd already picked up by trial and error and taught me 1000x as much more and I still don't feel I read more than 1% of the site
Technically speaking the dictionary would define Visual Basic users as programmers.
But here again, a very generalized, liberal definition is being employed and it's wrong
- just plain wrong - Tom Archer 5/12/02
|
|
|
|
|
i am using the following SQL statement to populate a datagrid in VB, it executes fine when i test it in access but it does not work in vb - what is the problem? thank you!
Dim strCompany As String
Dim strLikeCompany As String
strCompany = txtCompany.Text
strLikeCompany = strCompany & "*"
Dim findSQL As String
findSQL = " Select ClientCode, Company, ContactName " & _
" from Client " & _
" Where Company LIKE '" & strLikeCompany & "'"
Set RS = Conn.Execute(findSQL)
|
|
|
|
|
Are you definitely using the same strLikeCompany value when testing on Access and VB?
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
Hi !
I'd want to be able to visualize data from a huge database's table using a ComboBox (like Access ComboBoxes).
In fact, I've got 50000 records in my table (from MySQL using Odbc), the data loading time into the DataTable is quite good but when I want to dropdown the ComboBox, it takes 3 or 4 seconds to paint it.
My question is : Do you know a ComboBox control that can display the data progressively from its datasource member ? or Do you have another solution ?
Please help me, it's very important to get a faster painting time for my app.
Thanks
jpeg
|
|
|
|
|
Hi..
do you use a static cursor ?
I think you will get better results if you use dynamic cursor..
let me know if this works
|
|
|
|
|
Thank you for your fast answer !
First of all, I've forgotten to say that my program is written with C# and I use DataSet/DataTable classes.
Next, I'm a newbie with developing Databases' app and I don't know if I use a static or dynamic cursor. So, can you tell me how do you use static/dynamic cursor with these classes ?
jpeg
|
|
|
|
|
Hi..
As you are using C# so you don't use ADO .. you use ADO.NET which does not support dynamic cursor because ADO.NET is based on the disconnected model (you get all the data once then you close the connection)
so for your problem I recommend you to use DataReader
read this carefully:
in MSDN:
You can use the ADO.NET DataReader to retrieve a read-only, forward-only stream of data from a database. Using the DataReader can increase application performance and reduce system overhead because only one row at a time is ever in memory.
|
|
|
|
|
I'll try to fill my ComboBox "dynamically" with a DataReader object but I think it won't be easy because of the stream's 'Forward Only' property. 
I can't understand that the Microsoft Framework don't provide any class/control to visualize data dynamically & automatically in a control (old MS Access versions were able to do this) : It's incredible, don't you think ?
Thank you for your help.
jpeg
|
|
|
|
|
I agree with you that .NET has some strange things..data bound controls in the VB6 way was great..
ADO was much better than ADO.NET which I did not understand yet why it's based on the disconnected model, while ADO can give you the same functionality if you need..
So I never liked ADO.NET !!
|
|
|
|
|
jpeg wrote:
it takes 3 or 4 seconds to paint it
I tried to load 50,000 elements into a combobox and it painted instantly.
What machine are you running off?
When are you doing the actual load into the combobox? i.e. which event
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
My developing machine is a 1.7 Ghz Celeron. So it's a quite good machine. This is not the problem. Maybe the problem is that I use C#. The initializing code (which is located in the form's loading event-handler) is :
DataTable mytable=LoadMyTable();
myComboBox.DataSource=mytable;
myComboBox.DisplayMember="myfieldname";
All this code is fast (I see it in debugging mode) but the first painting takes a lot of time (Maybe because of the internal control initializing method). After the first painting, the dropdown takes a little time.
If you have a piece of code that permit the control to be painted instantly, I'm highly interested !!!
jpeg
|
|
|
|
|
Try and populate the combobox manually.
The delay may be in the data binding itself as painting a 50,000 element combox is very quick.
Cheers,
Simon
"The day I swan around in expensive suits is the day I hope someone puts a bullet in my head.", Chris Carter.
my svg article
|
|
|
|
|
Hi!
I want to do a simple SELECT on a table, returning the count of records containing a greater date than a certain date I write in the query expression.
Using Access, I'd simply write:
SELECT Count(1) As nMsg FROM Bacheca WHERE ([Date] > #2003/01/01 13:59:07#)
But that select statement returns an error when used in SQL Server, it says Syntax error near '13'. Can you tell me how to fix it?
And, I'd like to make that query work for both Access and SQL Server - is it possible?
Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)
|
|
|
|
|
Use ' instead of # and treat it as a string, e.g.:
SELECT Count(1) As nMsg FROM Bacheca WHERE ([Date] > '2003/01/01 13:59:07')
This won't work in Access though, AFAIK you'll need to know before hand and use the correct query for each one. Can anyone confirm or deny that?
|
|
|
|
|
Try using the ODBC timestamp format. It works in SQL server, and it should work for Access via ODBC/OLEDB. (I haven't tested it, though.)
SELECT
Count(1) As nMsg
FROM
Bacheca
WHERE
([Date] > {ts '2003-01-01 13:59:07'})
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
|
Yep it works in SQL Server, but in Access it fails... he takes it as a GUID... BTW, this is my Access connection string, just in case... thanks for your effort anyway 
adminDBConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("_private/db/skakkinostri.mdb")
Luca Leonardo Scorcia
http://zip.to/kojak (only in Italian)
|
|
|
|
|
How I can define that ADO Connection with MS SQL Server was failed before there are raised exceptions while ADO Command objects will run.
|
|
|
|
|
Whats your code and when does it happend?
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
I've an strange case where a second connection to a Sqlserver database hangs until time out has expired, the schema is below:
<br />
Connection1=New connection(database1)<br />
Connection1.Open<br />
Connection1.Begintrans<br />
...<br />
Connection2=New connection(database1)<br />
Connection2.Open<br />
Command2.Connection=Connection2<br />
Command2.ExecuteSelectionSQL |-----------------------here it hangs<br />
Connection2.Close<br />
.......<br />
Connection1.Commit<br />
Connection1.Close<br />
The first connection remains opened and in transaction while connection2 is opened with the same querystring, it opens correctly but when I issue a command using connection2 it hangs.
I use System.Data.SqlClient and I've tried different options in querystring (Enlist=false,.....) but I cannot solve the problem.
Thanks for your help,
Hzi.
|
|
|
|
|
seems like you have locking problems .. do you access the same tables ?
in both connections ?
|
|
|
|
|
Yes, when I'm accessing the same table, it hangs, but when I access another table it not hangs, but SQLServer locks a whole table when I insert a single record ?.
Thanks
|
|
|
|
|
No..
locking is not this simple..it differs according to how you access the table..
tell me the query so i can help !!
|
|
|
|
|
Here is a trace of the steps shown in sqltrace tool:
<br />
with the first connection<br />
exec sp_reset_connection<br />
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;BEGIN TRANSACTION<br />
sp_reset_connection<br />
update objectes set [Caducitat]=NULL, [DataValid]=NULL, [UltimaMod]=CONVERT(DATETIME, '2003-01-16T13:18:00', 126) where id=100225<br />
with the second connection<br />
sp_reset_connection<br />
select * from objectes where idref=''BAINtiA000046'' and IM1=1' <-------------It hangs here
It's all.
Thanks.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
