Data throughput

I am trying to understand what things affect the
throughput of data when queries are executed to SQL
Server. I'm trying to determine if there are
settings,pramiters or times that can improve the speed of
a recordset being returned to the application.
What I have noticed in our environment is that a
recordset of 1,000 rows is returned very quickly where as
a recordset of a 1,000,000,000 rows can take over an hour.
I understand that the bandwidth will have a large effect
on the time but there are times when the bandwidth is
wide open and it still takes a significant amount of
time. What things can I do/set to optimixe this as much
as possible.
What can you do? Don't return 1 billion row resultsets, quite simple. No
human is able to digest that amount of information, so you should filter or
summarize it at the server using WHERE, SUM etc, and then return a more
limited number of rows to the client.
Jacco Schalkwijk
SQL Server MVP
"Jim Abel" <jim.abel@.lmco> wrote in message
news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
> I am trying to understand what things affect the
> throughput of data when queries are executed to SQL
> Server. I'm trying to determine if there are
> settings,pramiters or times that can improve the speed of
> a recordset being returned to the application.
> What I have noticed in our environment is that a
> recordset of 1,000 rows is returned very quickly where as
> a recordset of a 1,000,000,000 rows can take over an hour.
> I understand that the bandwidth will have a large effect
> on the time but there are times when the bandwidth is
> wide open and it still takes a significant amount of
> time. What things can I do/set to optimixe this as much
> as possible.
|||The query is used to fill a CSV file that a third party
application uses to generate charts driven by the
ultimate Users. The reason I'm asking the question is to
see if there are options to speed the flow of data from
the Database to the CSV file to shorten the time it
currently takes. I can't modify the 3rd parties software
and I can't query less data so the ODBC seems like the
place to look. Your answer is logical for different
circumstances but doesn't get to the original question,
ARE there items that can be tweaked in ODBC that can
increase throughput?

>--Original Message--
>What can you do? Don't return 1 billion row resultsets,
quite simple. No
>human is able to digest that amount of information, so
you should filter or
>summarize it at the server using WHERE, SUM etc, and
then return a more[vbcol=seagreen]
>limited number of rows to the client.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jim Abel" <jim.abel@.lmco> wrote in message
>news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
of[vbcol=seagreen]
as[vbcol=seagreen]
hour.[vbcol=seagreen]
effect[vbcol=seagreen]
much
>
>.
>
|||What do you use to generate the csv file? bcp will be the quickest.
I--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:70ca01c42fae$ac9de360$a101280a@.phx.gbl...[vbcol=seagreen]
> The query is used to fill a CSV file that a third party
> application uses to generate charts driven by the
> ultimate Users. The reason I'm asking the question is to
> see if there are options to speed the flow of data from
> the Database to the CSV file to shorten the time it
> currently takes. I can't modify the 3rd parties software
> and I can't query less data so the ODBC seems like the
> place to look. Your answer is logical for different
> circumstances but doesn't get to the original question,
> ARE there items that can be tweaked in ODBC that can
> increase throughput?
>
> quite simple. No
> you should filter or
> then return a more
> of
> as
> hour.
> effect
> much

Data throughput

I am trying to understand what things affect the
throughput of data when queries are executed to SQL
Server. I'm trying to determine if there are
settings,pramiters or times that can improve the speed of
a recordset being returned to the application.
What I have noticed in our environment is that a
recordset of 1,000 rows is returned very quickly where as
a recordset of a 1,000,000,000 rows can take over an hour.
I understand that the bandwidth will have a large effect
on the time but there are times when the bandwidth is
wide open and it still takes a significant amount of
time. What things can I do/set to optimixe this as much
as possible.What can you do? Don't return 1 billion row resultsets, quite simple. No
human is able to digest that amount of information, so you should filter or
summarize it at the server using WHERE, SUM etc, and then return a more
limited number of rows to the client.
Jacco Schalkwijk
SQL Server MVP
"Jim Abel" <jim.abel@.lmco> wrote in message
news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
> I am trying to understand what things affect the
> throughput of data when queries are executed to SQL
> Server. I'm trying to determine if there are
> settings,pramiters or times that can improve the speed of
> a recordset being returned to the application.
> What I have noticed in our environment is that a
> recordset of 1,000 rows is returned very quickly where as
> a recordset of a 1,000,000,000 rows can take over an hour.
> I understand that the bandwidth will have a large effect
> on the time but there are times when the bandwidth is
> wide open and it still takes a significant amount of
> time. What things can I do/set to optimixe this as much
> as possible.|||The query is used to fill a CSV file that a third party
application uses to generate charts driven by the
ultimate Users. The reason I'm asking the question is to
see if there are options to speed the flow of data from
the Database to the CSV file to shorten the time it
currently takes. I can't modify the 3rd parties software
and I can't query less data so the ODBC seems like the
place to look. Your answer is logical for different
circumstances but doesn't get to the original question,
ARE there items that can be tweaked in ODBC that can
increase throughput?

>--Original Message--
>What can you do? Don't return 1 billion row resultsets,
quite simple. No
>human is able to digest that amount of information, so
you should filter or
>summarize it at the server using WHERE, SUM etc, and
then return a more
>limited number of rows to the client.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jim Abel" <jim.abel@.lmco> wrote in message
>news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
of[vbcol=seagreen]
as[vbcol=seagreen]
hour.[vbcol=seagreen]
effect[vbcol=seagreen]
much[vbcol=seagreen]
>
>.
>|||What do you use to generate the csv file? bcp will be the quickest.
I--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:70ca01c42fae$ac9de360$a101280a@.phx.gbl...[vbcol=seagreen]
> The query is used to fill a CSV file that a third party
> application uses to generate charts driven by the
> ultimate Users. The reason I'm asking the question is to
> see if there are options to speed the flow of data from
> the Database to the CSV file to shorten the time it
> currently takes. I can't modify the 3rd parties software
> and I can't query less data so the ODBC seems like the
> place to look. Your answer is logical for different
> circumstances but doesn't get to the original question,
> ARE there items that can be tweaked in ODBC that can
> increase throughput?
>
>
> quite simple. No
> you should filter or
> then return a more
> of
> as
> hour.
> effect
> much

Data Mining : Drug Trial Question

I have a a simple situation. I am trying to determine for how long (in months) two drugs should be given to a patient. Both the drugs are administered at frequent intervals and their dosage and the condition of the patient is recorded. How do I determine for how long should I continue to adminster the drugs,at what point should I stop? I am dealing with the following variables : Drug 1 dosage,Drug 2 dosage, Period of medication (in months). Can I use SQL Server 2005 data mining capabilites to get the answer? Please let me know.

No you cannot. An MD should evaluate the patient and decide to stop or increase/decrease dosage. Technology is yet far from of replacing an MD.|||

I have to agree with the other response that this decision should be made by a qualified MD. What data mining can do is tell you the relationships between the dosages and the medication periods. Note that since these results are dependent on data, and in this case the data is determined by the recommendations of MD's, essentially what the algorithm is doing is determining patterns in behavior of MD's prescribing drugs for an array of patients. The results could be skewed by a number of factors. For example, if all of the data came from a single MD, you would only be seeing the pattern of that doctor's prescription behavior.

What could the results be used for? I believe in this case, you could use the results for budgeting and forecasting - for example, you see the dosages and you can give an estimate to a patient as to when their treatment would end or to a payment provider as to the total cost of treatment. Another usage would be to present the patterns to MD's - e.g. as a logistic regression scorecard, tree, or some other easily consumable form - that they could use as a guideline to help them make decisions.

However, as the other responder noted, any decisions should rest in the hands of a qualified MD.

|||

Thank you, Jamie for responding to the question. I think you have very well summarized the solution, I am looking for. I am looking to find the dosages so that I can give an estimate to a patient as to when their treatment would end(an interval estimate) or to a payment provider as to the total cost of treatment (an interval estimate). Can I use logistic regression for the purpose ?

|||You could use Logistic Regression, Neural Nets, or Decision Trees. DT will give the most understandable model. I would try all three and use the accuracy chart to see which model does the best job at prediction.

Data Load Query

Hi,

I'm extracting data from a mainframe application with a view to loading
it into a MS SQL database. I'm trying to determine the most efficient
way to format the mainframe extract file to make loading into the
database easier.

The problem I have is that the existing record structure includes an
array that can vary between 1 to 50. If I include this array in a
single record the table I use to import the data would need 50 columns
though not all these would be populated. There is a field in the record
to identify how many occurances of the array there are.

Current Record Structure :
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated
Array :
Value1
Value2
Value3
...
up to Value50 (if required)

i.e.

12344,Mr Agent,$29.95,2,BX123,BX124
12345,Mr Jones,$14.95,3,XX123,XX124,XX125
12345,Mr Jones,$14.00,1,XY123
12345,Mr Jones,$15.95,2,XZ124,XZ125
12346,Mr Smith,$19.95,3,AX123,AX124,AX125
12346,Mr Smith,$19.00,1,BY123
12347,Mr Acant,$99.95,7,CX123,CX124,CX125,CX126,CX127,CX128 ,CX129

There may be up to 3 records created for each Account Number with
different values in the array fields.

Am I better to break this file into two files .. one with the core
customer information and a second file with a row for each array value
which has a link to the customer information file.
Or
Is there a way to efficiently process the original file once it is
loaded into the staging tables in the database ?

i.e.

File 1 - Core Customer Information
====================================
Current Record Structure :
Record Number
Account Number
Account Name
Other Account Details
TotalNumberOfArrayFieldsPopulated

File 2 - Array Information
====================================
Record Number
Array :
Value1
Value2
Value3
...
up to Value50 (if required)

File 1
========================
12344,Mr Agent,$29.95,2
12345,Mr Jones,$14.95,3
12345,Mr Jones,$14.00,1
12345,Mr Jones,$15.95,2
12346,Mr Smith,$19.95,3
12346,Mr Smith,$19.00,1
12347,Mr Acant,$99.95,7

File 2
========================
12344,BX123
12344,BX124
12345,XX123
12345,XX124
12345,XX125
12345,XY123
12345,XZ124
12345,XZ125
12346,AX123
12346,AX124
12346,AX125
12346,BY123
12347,CX123
12347,CX124
12347,CX125
12347,CX126
12347,CX127
12347,CX128
12347,CX129

At times the individual array values will be used for look ups though
essentially the Customer Information record will be the primary lookup
data.

I'm leaning toward changing my COBOL code and creating the 2nd output
unless someone can suggest a simple way to process the information once
loaded into the table.

Any help that could be suggested would be greatly appreciated.For a varying number of fields per record, you might consider XML, as
it's a good format for that kind of data. But I have to say that I have
very limited experience of importing XML data into MSSQL myself - check
out OPENXML in Books Online, the SQLXML tools from Microsoft (which
include an XML bulk load COM component), and you could also post in
microsoft.public.sqlserver.xml to get some feedback on that approach.

Simon

Data Insert via SQL Express using VB Issue

I've been doing a lot of research lately to determine the most secure way to issue an insert statement to insert my data into my SQL database. I'm coding this following using Visual Basic:

Protected Sub submitButton_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Dim strConnAs String ="server=.\SQLEXPRESS;database=C:\SJ-site\SJ\App_Data\SJDb.mdf; User Id=user; password=password"Dim MyQueryAs String ="Insert into sjTable (PostID, Category, TitleofPost, Description, Price, Loc, ContactInfo, DatePosted, Disclaimer) Values (@.Category, @.TitleofPost, @.Description, @.Price, @.Loc, @.ContactInfo, @.DatePosted)"Dim MyConnAs New Data.SqlClient.SqlConnection(strConn)Dim cmdAs New Data.SqlClient.SqlCommand(MyQuery, MyConn)Dim curDateAs String On Error Resume Next MyConn.Open()If Err.Number <> 0Then MsgBox("There was an error connecting to the database.", MsgBoxStyle.OkOnly) Err.Number = 0End If curDate = Now()With cmd.Parameters .Add(New System.Data.SqlClient.SqlParameter("@.Category", categoryDropDown.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.TiteofPost", titleTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Description", descriptionTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Price", priceTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Loc", locationTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.ContactInfo", contactTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.DatePosted", curDate))End With If Err.Number <> 0Then MsgBox("There was an error inserting the data into the database.", MsgBoxStyle.OkOnly) Err.Number = 0End If MyConn.Close()If Err.Number <> 0Then MsgBox("There was an erro closing the database connection.", MsgBoxStyle.OkOnly) Err.Number = 0End If Server.Transfer("submitted.aspx")End Sub

The code gives no errors, it directs me straight to thesubmitted.aspx page but when I query the database it is not insertingany data into my database. .

My Error log shows the following:
2006-06-27 12:14:16.68 spid51 Starting up database 'C:\SJ-SITE\SJ\APP_DATA\SJDB.MDF'.
2006-06-27 12:14:26.93 Logon Error: 17828, Severity: 20, State: 3.
2006-06-27 12:14:26.93 Logon The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: <local machine>]

I am able to log into the database using my account via SQL Server Management Studio Express. I've been all over the forums and google looking for a solution but everything I've been trying to mock up from other people's code hasn't helped. Thanks in advance for your assistance

Hi, do you have 17190 error in your SQL ERRORLOG when SQL startup?

2006-03-24 10:37:09.80 Server Error: 17190, Severity: 16, State: 1.
2006-03-24 10:37:09.80 Server FallBack certificate initialization failed with error code: 1.
2006-03-24 10:37:09.81 Server Warning:Encryption is not available, could not find a valid certificate to load.

And is your SQL service startup account a local account whose password has been changed recently?

If so, try to delete the file in folder

C:\Documents and Settings\<SQL Server startup
account>\Application Data\Microsoft\Crypto\RSA\S-1-5-21-963106725-2405722570-177647575-1023

The last part (S-1-5-21-963106725-2405722570-177647575-1023) may be different for
every account. Then restartSQL Server services to take effect.

|||I went ahead and upgraded to SQL Express 2005 SP1 w/ Advanced Services. I re-built my user accounts and what not. Now, I am still getting no error messages but also when I open the log file the last thing it says is:
2006-06-28 09:06:57.93 spid51 Starting up database 'C:\SJ-SITE\SJ\APP_DATA\SUGARJACKS.MDF'.

The data from the forms still isn't inserting into the database. The code hasn't changed and like I said the only thing I did was upgrade the SQL Express and re-build the user account.
|||On a side note I just wanted to say that yes I did change the database name and yes I did update the connection string accordingly in the code.