Good or Bad Idea? Multiple Tables for Data Import

Hello all,
We have an application which runs on a campaign basis. Data is loaded
throughout the day into our application. We typically load 20K - 30K rows
of data per campaign per day (total of about 150K - 200K row of data per
day).
We typically run about 30 - 35 campaigns simultaneously.
In order to increase the performance of our application, I was thinking of
loading each campaign into it's own table. The campaign tables would all be
identical. This would allow us to index each of these tables before each
campaign run to ensure the best possible performance.
What do you guys think?
We're finding that our application is being bogged down - we need to query
the tables continously as the application run... and return record sets
with minimal time.
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
Lucas
Have you read "Partitioned View" article in the BOL? If I understood you
correctly , that what you need .
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D12EDD5C9ECnntprogerscom@.127.0.0.1...
> Hello all,
> We have an application which runs on a campaign basis. Data is loaded
> throughout the day into our application. We typically load 20K - 30K rows
> of data per campaign per day (total of about 150K - 200K row of data per
> day).
> We typically run about 30 - 35 campaigns simultaneously.
> In order to increase the performance of our application, I was thinking of
> loading each campaign into it's own table. The campaign tables would all
> be
> identical. This would allow us to index each of these tables before each
> campaign run to ensure the best possible performance.
> What do you guys think?
> We're finding that our application is being bogged down - we need to query
> the tables continously as the application run... and return record sets
> with minimal time.
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:

> Lucas
> Have you read "Partitioned View" article in the BOL? If I understood
> you correctly , that what you need .
Thanks URI, that seems to be what we need.
From your experience, would we gain a lot of performance by segmenting data
into it's own table?
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
|||Hi
Yes, if you have appropriate indexes defined on the table you will be
benefit from performance.
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D180999FD22nntprogerscom@.127.0.0.1...
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:
>
> Thanks URI, that seems to be what we need.
> From your experience, would we gain a lot of performance by segmenting
> data
> into it's own table?
>
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

Good or Bad Idea? Multiple Tables for Data Import

Hello all,
We have an application which runs on a campaign basis. Data is loaded
throughout the day into our application. We typically load 20K - 30K rows
of data per campaign per day (total of about 150K - 200K row of data per
day).
We typically run about 30 - 35 campaigns simultaneously.
In order to increase the performance of our application, I was thinking of
loading each campaign into it's own table. The campaign tables would all be
identical. This would allow us to index each of these tables before each
campaign run to ensure the best possible performance.
What do you guys think?
We're finding that our application is being bogged down - we need to query
the tables continously as the application run... and return record sets
with minimal time.
Thanks.
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.comLucas
Have you read "Partitioned View" article in the BOL? If I understood you
correctly , that what you need .
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D12EDD5C9ECnntprogerscom@.127.0.0.1...
> Hello all,
> We have an application which runs on a campaign basis. Data is loaded
> throughout the day into our application. We typically load 20K - 30K rows
> of data per campaign per day (total of about 150K - 200K row of data per
> day).
> We typically run about 30 - 35 campaigns simultaneously.
> In order to increase the performance of our application, I was thinking of
> loading each campaign into it's own table. The campaign tables would all
> be
> identical. This would allow us to index each of these tables before each
> campaign run to ensure the best possible performance.
> What do you guys think?
> We're finding that our application is being bogged down - we need to query
> the tables continously as the application run... and return record sets
> with minimal time.
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:

> Lucas
> Have you read "Partitioned View" article in the BOL? If I understood
> you correctly , that what you need .
Thanks URI, that seems to be what we need.
From your experience, would we gain a lot of performance by segmenting data
into it's own table?
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Hi
Yes, if you have appropriate indexes defined on the table you will be
benefit from performance.
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D180999FD22nntprogerscom@.127.0.0.1...
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:
>
>
> Thanks URI, that seems to be what we need.
> From your experience, would we gain a lot of performance by segmenting
> data
> into it's own table?
>
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

Good or Bad Idea? Multiple Tables for Data Import

Hello all,
We have an application which runs on a campaign basis. Data is loaded
throughout the day into our application. We typically load 20K - 30K rows
of data per campaign per day (total of about 150K - 200K row of data per
day).
We typically run about 30 - 35 campaigns simultaneously.
In order to increase the performance of our application, I was thinking of
loading each campaign into it's own table. The campaign tables would all be
identical. This would allow us to index each of these tables before each
campaign run to ensure the best possible performance.
What do you guys think?
We're finding that our application is being bogged down - we need to query
the tables continously as the application run... and return record sets
with minimal time.
Thanks.
--
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.comLucas
Have you read "Partitioned View" article in the BOL? If I understood you
correctly , that what you need .
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D12EDD5C9ECnntprogerscom@.127.0.0.1...
> Hello all,
> We have an application which runs on a campaign basis. Data is loaded
> throughout the day into our application. We typically load 20K - 30K rows
> of data per campaign per day (total of about 150K - 200K row of data per
> day).
> We typically run about 30 - 35 campaigns simultaneously.
> In order to increase the performance of our application, I was thinking of
> loading each campaign into it's own table. The campaign tables would all
> be
> identical. This would allow us to index each of these tables before each
> campaign run to ensure the best possible performance.
> What do you guys think?
> We're finding that our application is being bogged down - we need to query
> the tables continously as the application run... and return record sets
> with minimal time.
> Thanks.
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||"Uri Dimant" <urid@.iscar.co.il> wrote in
news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:
> Lucas
> Have you read "Partitioned View" article in the BOL? If I understood
> you correctly , that what you need .
Thanks URI, that seems to be what we need.
From your experience, would we gain a lot of performance by segmenting data
into it's own table?
Lucas Tam (REMOVEnntp@.rogers.com)
Please delete "REMOVE" from the e-mail address when replying.
Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com|||Hi
Yes, if you have appropriate indexes defined on the table you will be
benefit from performance.
"Lucas Tam" <REMOVEnntp@.rogers.com> wrote in message
news:Xns96D180999FD22nntprogerscom@.127.0.0.1...
> "Uri Dimant" <urid@.iscar.co.il> wrote in
> news:#O9$T2QuFHA.2076@.TK2MSFTNGP14.phx.gbl:
>> Lucas
>> Have you read "Partitioned View" article in the BOL? If I understood
>> you correctly , that what you need .
>
> Thanks URI, that seems to be what we need.
> From your experience, would we gain a lot of performance by segmenting
> data
> into it's own table?
>
> --
> Lucas Tam (REMOVEnntp@.rogers.com)
> Please delete "REMOVE" from the e-mail address when replying.
> Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com

Going nuts here!

Alright- I plugged this code in to tell my form to write to SQLServer when I click the Submit button. However I error out when I load the form:

dim Conn as new OleDbConnection("DSN=MyDB")

Sub On_Click(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "'," & _
"'" & params(1) & "'," & _
"'" & params(2) & "'")

ExecuteStatement(strSQL)
end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand(strSQL, Conn)

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as Exception
end try
objCmd.Connection.Close()
end function

I get this error:
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30471: Expression is not an array or a method, and cannot have an argument list.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~~~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(14) : error BC30205: End of statement expected.

strSQL="INSERT INTO tblSCRequest "(strRequestor, dtRequestDate, strAudience) VALUES (" & _
~~~~~~~~~~~~~
C:\Inetpub\wwwroot\SOSComm\CommWeb4.aspx(15) : error BC30035: Syntax error.

"'" & params(0) & "'," & _

Any ideas??try this :

strSQL="INSERT INTO tblSCRequest (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

better yet, use parameterized queries. (1) you dont have to worry about these quotes (2) you are safe from sql injection attacks.

hth|||So what if for simplicity sake I try this-

strSQL=""INSERT INTO tblSCRequest (strRequestor) VALUES (" & _
";" & params (0) & "')"

Would this work? Also, I saw your link to the param site. If I get this working I'm going to use that going forward. Thanks.|||no a ; indicates end of sql statement. use parameterized queries. thats the best way to go about it.

hth|||Okay. I have this problem now-

Sub Submit(obj as object, e as EventArgs)
dim params(3) as String
dim strSQL as String

strSQL="INSERT INTO Comm (strRequestor, dtRequestDate, strAudience) VALUES (" & _
"'" & params(0) & "','" & params(1) & "','" & params(2) & " ')"

ExecuteStatement(strSQL)

end sub

function ExecuteStatement(strSQL)
dim objCmd as new OleDbCommand()

try
objCmd.Connection.Open()
objCmd.ExecuteNonquery()
catch ex as exception
end try
objCmd.Connection.Close()
end function

I get an error saying "System.NullReferenceException: Object reference not set to an instance of an object." And it is pointing to line 31 as the culprit-

Line 29: catch ex as exception
Line 30: end try
Line 31: objCmd.Connection.Close()<---Error line
Line 32: end function|||(1) use parameterized queries. ( i suggested this earlier too)
(2) make sure you have values in all the parameters.
(3) put the connection.close in the finally block

hth

Global Variables

Hello,
Im having a little problem(i hope) with global variables.
Im working with the DTS of the SQL SERVER 2000! Does someone knows how can i load global variables using an SQL Task!?
Thank you all!!
Kind Regards,
LULUin package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des|||Originally posted by DesmondX
in package properties, add global variable x with correct type
create execute sql task , select the value you want to
populate your global variable,click on parameters in the exec sql task properties,
click on output parameters,set type (row,rowset), map the output to the correct global variable.
To use the variable, in a exec sql task, click on parameters,
get your global variable, map it to parameter 1
then in query use a ? to reference the global variable.
-des

Thanks.... DESMONDX

Global Load Balancing

Can anyone guide to where I can find the advantages and
disadvantages of GLB on SQL 2000, thanks826294 SQL Server 2000 High Availability Series Chapter Discusses a
http://support.microsoft.com/?id=826294
327518 INF: The Microsoft Support Policy for a SQL Server Failover Cluster
http://support.microsoft.com/?id=327518
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.