advice on querying large tables

I have to deal with a new database with a number of tables with 100,000+
records. Can anybody give me any advice on the different methods of
querying large tables as opposed to small tables?
Cheers,
Mike
*** Sent via Developersdex http://www.examnotes.net ***"Mike P" <mike.parr@.gmail.com> wrote in message
news:eEhRZu8tFHA.360@.TK2MSFTNGP12.phx.gbl...
>I have to deal with a new database with a number of tables with 100,000+
> records. Can anybody give me any advice on the different methods of
> querying large tables as opposed to small tables?
Learn to use the WHERE clause.
Learn how indexes work (in depth) and then build them appropriately for your
queries. Take a look at the index tuning wizard for additional help there.
Only ask for the data you actually need.
This should get you started..
Rick Sawtell
MCT, MCSD, MCDBA|||Learn how to use the Show Execution Plan feature of Query Analyzer.
"Mike P" <mike.parr@.gmail.com> wrote in message
news:eEhRZu8tFHA.360@.TK2MSFTNGP12.phx.gbl...
>I have to deal with a new database with a number of tables with 100,000+
> records. Can anybody give me any advice on the different methods of
> querying large tables as opposed to small tables?
>
> Cheers,
> Mike
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Regarding how learning about indexes and execution plans, I have found the
following book to be very helpful:
Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, by
Ken England
"Mike P" wrote:

> I have to deal with a new database with a number of tables with 100,000+
> records. Can anybody give me any advice on the different methods of
> querying large tables as opposed to small tables?
>
> Cheers,
> Mike
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Yes, I have that one too and found it informative.
"Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
news:FBBB8707-300C-4510-BE0E-CDE1D4408B75@.microsoft.com...
> Regarding how learning about indexes and execution plans, I have found the
> following book to be very helpful:
> Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook, by
> Ken England
> "Mike P" wrote:
>|||I found this book better....
"SQL Server Query: Performance Tuning Distilled" by Sajal Dam (Curlingstone
Publisher)
"JT" wrote:

> Yes, I have that one too and found it informative.
> "Boddhicitta" <Boddhicitta@.discussions.microsoft.com> wrote in message
> news:FBBB8707-300C-4510-BE0E-CDE1D4408B75@.microsoft.com...
>
>sql

ado-sql-oledb

Hi
Im using VB6,ADO, OLEDB and SQL Server 2000/2005
On any SQL error the error number from Error is -2147221504
How to get the "real" error number to specifiy error
For example if "Time Out" happens then error is 10021 or something
aso.
regards;
Mex
"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uIz8nYfGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
Enumerate the ADODB.Connection.Errors Collection
http://support.microsoft.com/kb/168336
http://support.microsoft.com/kb/168354
hth
-ralph
|||http://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx
|||Meelis Lilbok wrote:
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
This may help:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ado-sql-oledb

Hi
Im using VB6,ADO, OLEDB and SQL Server 2000/2005
On any SQL error the error number from Error is -2147221504
How to get the "real" error number to specifiy error
For example if "Time Out" happens then error is 10021 or something
aso.
regards;
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uIz8nYfGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
Enumerate the ADODB.Connection.Errors Collection
http://support.microsoft.com/kb/168336
http://support.microsoft.com/kb/168354
hth
-ralph|||http://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx|||Meelis Lilbok wrote:
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
This may help:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ado-sql-oledb

Hi
Im using VB6,ADO, OLEDB and SQL Server 2000/2005
On any SQL error the error number from Error is -2147221504
How to get the "real" error number to specifiy error
For example if "Time Out" happens then error is 10021 or something
aso.
regards;
Mex"Meelis Lilbok" <meelis.lilbok@.deltmar.ee> wrote in message
news:uIz8nYfGHHA.4580@.TK2MSFTNGP05.phx.gbl...
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
Enumerate the ADODB.Connection.Errors Collection
http://support.microsoft.com/kb/168336
http://support.microsoft.com/kb/168354
hth
-ralph|||http://msdn2.microsoft.com/en-us/library/aa905919(sql.80).aspx|||Meelis Lilbok wrote:
> Hi
> Im using VB6,ADO, OLEDB and SQL Server 2000/2005
> On any SQL error the error number from Error is -2147221504
> How to get the "real" error number to specifiy error
> For example if "Time Out" happens then error is 10021 or something
> aso.
>
This may help:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

ADO MaxRecords and table variables

It appears that when I specify the MaxRecords property in ADO to something other than 0, not only does SQL Server 2000 limit the number of rows that are being returned from a stored procedure, it also limits the number of "select into"s within the stored proecudre (I'm inserting into table variables within the proc).

Here's a scenario of why this is a problem. Suppose I have a proc that needs to perform calculations on all employees in my system (10,000 of them). The calculation is performed by selecting these employees into a table variable with some calculated values being inserted as well. When the processing is complete, I need to select the top 1500 employees from the table.

If I specify MaxRecords in ADO, the "select into" statement that populates my table variable appears to limit the number of rows to 1500. This is a problem if I'm expecting an employee outside of the first 1500 to be considered for the calculations I'm performing.

Is there some way of indicating that MaxRecords should be ignored when selecting into table variables or temporary tables within a stored proc?

Thanks,
TerenceI know I could simply use "top" in the last select statement of the stored procedure to get around this problem, however, this is not an option on the system I'm currently developing (for design reasons (possibly poor design reasons now that I think about it ;) ))

Thanks again,
Terence

ADO 2.8 Is there any limitation on number of columns?

Hi,

I'm using ADO 2.8 in a vb.net code. The .Net framework version is 1.1 and windows server 2003.

I'm firing a query that result in 256 columns and few hundred rows. Here is the snapshot of the code

adoRs = New ADODB.Recordset

With adoRs

.CursorLocation = CursorLocationEnum.adUseClient ' adUseClient

.ActiveConnection = adoCn

.CursorType = CursorTypeEnum.adOpenStatic ' adOpenStatic

.LockType = LockTypeEnum.adLockBatchOptimistic ' adLockBatchOptimistic

.Open(strSQL)

End With

The returned record set is empty with all the the 256 columns name. Could anyone shed light why it is returning empty recordset. Is there any limitation on number of columns that a recordset can hold.

Thanks in advance.

With regards

Ganesh

Is it possible for you to try limiting the number of columns?
If it is, you could easily experiment and see whether adding additional columns to the resultset makes it to be empty or not.|||

It doesn't seem like you have a limitation on the number of columns because you do get all the columns back, just the entire recordset is empty. It could mean that the query doesn't return any results. Could you run your query in management studio to verify that it actually returns some rows that you are missing via ADO. If so, please include the query and we would be able to assist you further.

HTH,

|||

The Query return few hundred rows. I have checked that and there is nothing wrong with the query. In .Net 2.0 it returns some data in the recordset and it behaves abnormally.But our present environment is 1.1

With regards

Ganesh

ADO 2.8 Is there any limitation on number of columns?

Hi,

I'm using ADO 2.8 in a vb.net code. The .Net framework version is 1.1 and windows server 2003.

I'm firing a query that result in 256 columns and few hundred rows. Here is the snapshot of the code

adoRs = New ADODB.Recordset

With adoRs

.CursorLocation = CursorLocationEnum.adUseClient ' adUseClient

.ActiveConnection = adoCn

.CursorType = CursorTypeEnum.adOpenStatic ' adOpenStatic

.LockType = LockTypeEnum.adLockBatchOptimistic ' adLockBatchOptimistic

.Open(strSQL)

End With

The returned record set is empty with all the the 256 columns name. Could anyone shed light why it is returning empty recordset. Is there any limitation on number of columns that a recordset can hold.

Thanks in advance.

With regards

Ganesh

Is it possible for you to try limiting the number of columns?
If it is, you could easily experiment and see whether adding additional columns to the resultset makes it to be empty or not.|||

It doesn't seem like you have a limitation on the number of columns because you do get all the columns back, just the entire recordset is empty. It could mean that the query doesn't return any results. Could you run your query in management studio to verify that it actually returns some rows that you are missing via ADO. If so, please include the query and we would be able to assist you further.

HTH,

|||

The Query return few hundred rows. I have checked that and there is nothing wrong with the query. In .Net 2.0 it returns some data in the recordset and it behaves abnormally.But our present environment is 1.1

With regards

Ganesh