bind variables / parameter queries

Hi,

I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.

Does anyone know how I can do this in SQL Server?

For instance, I have 20,000 employees and I'm pulling info by SS#:

Select * from EmpTable where SS_number = [SSN]

Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?

Thanks.write a stored procedure, and instead of calling the database engine 20,000 times, just call it once and pass it a list of 20,000 numbers

come to think of it, where would you get 20,000 numbers? sounds like you might want to look for a JOIN solution|||SQL Server actually goes you one better, in that its ODBC drivers will automagically parameterize a query for you (unless you get really creative in modifying the query).

As Rudy pointed out though, if you have more than 20 iterations from a given client, you really ought to be thinking about a JOIN based solution... Doing that kind of thing on that scale one row at a time is WAY too much work for me!

-PatP|||Thanks, guys. I'll get write access to the backend and write a stored proc.

I'll have to read up on how to pass values to the proc (I'm guessing it's like a function).

Thanks again.|||How about this?:

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "DSN=PKRebate2001", "sa", ""
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "sp_UpdateCustomerUnique"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ImportMonth").Value = IM
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing|||If you are looking to get automatic parameterization, that VBA code is conceptually good.

-PatP

Big Picture Advice please to a SQL 2000 intermediate/beginner user

I am migrating a Mid-Frame DB to SQL Server 2000 PC.
I will call oldDB, OldTables the ones that belong to the Mid-Frame.
I will call newDB, newTables the ones that belong to the PC.
The oldDB has many versions and I have retrieved the oldTables to CSV
Files and then I use DTS to make a oldTables as tempTables in SQL 2000
PC.
I need to transform data and process the columns in tempTables to
create the NewTables (ie Final Tables).
I have used SQL scripts with UDFs to accomplish one run for one
version of OldDB to newDB.
The question is : Should I use ADO.NET or embedded SQL to process the
TemPTables
And CSV Files or Should stick with SQL SELECT, INSERT, UPDATE scripts
to do this ?
I have used SQL scripts with a dozen UDFs, is it time to start using
other tools /objects like SPs ?
GigaThanksInAdvanceGiga,
IMHO, if it works for you the way you're doing it, then I reckon stick with
it until you're comfortable moving to other features of SQL like stored
procs.
ADO.NET or SQL to me makes no diff if the end result is the same. You might
find things process quicker with native SQL commands. And there's not much
difference between scripts which run your UDFs or SPs to run them. Scripts
you run in QA will give you more of a visual on what's going on.
cheers
Danny
<placidite1@.yahoo.com> wrote in message
news:1179264486.895840.101790@.n59g2000hsh.googlegroups.com...
>I am migrating a Mid-Frame DB to SQL Server 2000 PC.
> I will call oldDB, OldTables the ones that belong to the Mid-Frame.
> I will call newDB, newTables the ones that belong to the PC.
> The oldDB has many versions and I have retrieved the oldTables to CSV
> Files and then I use DTS to make a oldTables as tempTables in SQL 2000
> PC.
> I need to transform data and process the columns in tempTables to
> create the NewTables (ie Final Tables).
> I have used SQL scripts with UDFs to accomplish one run for one
> version of OldDB to newDB.
> The question is : Should I use ADO.NET or embedded SQL to process the
> TemPTables
> And CSV Files or Should stick with SQL SELECT, INSERT, UPDATE scripts
> to do this ?
> I have used SQL scripts with a dozen UDFs, is it time to start using
> other tools /objects like SPs ?
>
> GigaThanksInAdvance
>

Big Picture Advice please to a SQL 2000 intermediate/beginner user

I am migrating a Mid-Frame DB to SQL Server 2000 PC.
I will call oldDB, OldTables the ones that belong to the Mid-Frame.
I will call newDB, newTables the ones that belong to the PC.
The oldDB has many versions and I have retrieved the oldTables to CSV
Files and then I use DTS to make a oldTables as tempTables in SQL 2000
PC.
I need to transform data and process the columns in tempTables to
create the NewTables (ie Final Tables).
I have used SQL scripts with UDFs to accomplish one run for one
version of OldDB to newDB.
The question is : Should I use ADO.NET or embedded SQL to process the
TemPTables
And CSV Files or Should stick with SQL SELECT, INSERT, UPDATE scripts
to do this ?
I have used SQL scripts with a dozen UDFs, is it time to start using
other tools /objects like SPs ?
GigaThanksInAdvanceGiga,
IMHO, if it works for you the way you're doing it, then I reckon stick with
it until you're comfortable moving to other features of SQL like stored
procs.
ADO.NET or SQL to me makes no diff if the end result is the same. You might
find things process quicker with native SQL commands. And there's not much
difference between scripts which run your UDFs or SPs to run them. Scripts
you run in QA will give you more of a visual on what's going on.
cheers
Danny
<placidite1@.yahoo.com> wrote in message
news:1179264486.895840.101790@.n59g2000hsh.googlegroups.com...
>I am migrating a Mid-Frame DB to SQL Server 2000 PC.
> I will call oldDB, OldTables the ones that belong to the Mid-Frame.
> I will call newDB, newTables the ones that belong to the PC.
> The oldDB has many versions and I have retrieved the oldTables to CSV
> Files and then I use DTS to make a oldTables as tempTables in SQL 2000
> PC.
> I need to transform data and process the columns in tempTables to
> create the NewTables (ie Final Tables).
> I have used SQL scripts with UDFs to accomplish one run for one
> version of OldDB to newDB.
> The question is : Should I use ADO.NET or embedded SQL to process the
> TemPTables
> And CSV Files or Should stick with SQL SELECT, INSERT, UPDATE scripts
> to do this ?
> I have used SQL scripts with a dozen UDFs, is it time to start using
> other tools /objects like SPs ?
>
> GigaThanksInAdvance
>

Bestpractice for varying outputformat

Hello

I need an advice for an easy maintainable and highly flexible solution using SSIS. We're supplying our customers with an exportservice. The data is extracted from different tables, all with the same tableschema, fairly easy to create a foreach-container and iterate through the tablenames and extract data and use a flatfile destination to write it to...

But the not so easy part is to have different exportformats and still be using only one package. I could maybe use different ConnectionManagers for the FlatFileDestination, but that's not a very easy maintainable solution in my world. Our Customers will soon demand a webinterface where they can select necessary columns and apply different formatting for example they would demand the datetime to be '2006-12-24' or maybe '12/24/2006' etc. for alot of the available columns.

Any good suggestion on how to accomplish such a task?

Kind regards

Dot.Help any advices?

Best way to index my simple join table:

Hi,
I was hoping that someone would give me advice on the best way to index
my tables, for this situtation.
I have two tables, tblVisit and tblTechnician.
tblVisit has this primary key
VisitID int Identity(1,1)
tblTechnician has this primary key:
TechnicianID int Identity(1,1)
They are joined by a third table tblVisitTechnician which links the
two, enabling a visit to have more than one technician allocated:
tblVisitTechnician is structured:
VisitTechnicianID int Identity(1,1) - PrimaryKey
VisitRef int
TechnicianRef int
and both VisitRef and TechnicianRef are both setup as foriegn keys.
The problem I have is that any query I run which uses this join tbale
seems to get slowed down significantly.
What is the best to index these tables, to get the best performance?
Thanks
Alex Stevens
That not that much information for suggesting something to you... What does
the query plan show you ?
To extract the query plan for a query, issue the follwing statement in QA:
SET SHOWPLAN_TEXT ON
<Youqruery>
SET SHOWPLAN_TEXT OFF
Like that:
USE NORTHWIND
GO
SET SHOWPLAN_ALL ON
GO
Select * from Orders
GO
SET SHOWPLAN_ALL OFF
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Chubby Arse" <alexstevens@.gcc.co.uk> schrieb im Newsbeitrag
news:1116238813.680762.296230@.g44g2000cwa.googlegr oups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>
|||I don't think you need the VisitTechnicianID column, (VisitRef,
TechnicianRef) is the real primary key of this table, and I doubt that
adding an identity column to a table like this adds any value. As you
probably query on both Visit and Technician regularly, I would do it as
follows (using my preferred naming conventions):
CREATE TABLE VisitsTechnicians (
VisitID int NOT NULL,
TechnicianID int NOT NULL,
CONSTRAINT PK_VisitsTechnicians PRIMARY KEY (VisitID, TechnicianID),
CONSTRAINT FK_VisitsTechnicians__tblVisit
FOREIGN KEY (VisitID )
REFEENCES tblVisit (VisitID ),
CONSTRAINT FK_VisitsTechnicians__tblTechnician
FOREIGN KEY (TechnicianID )
REFEENCES tblVisit (TechnicianID )
)
CREATE NONCLUSTERED INDEX ix_VisitsTechnicians__TechnicianID__VisitID
ON VisitsTechnicians (VisitID , TechnicianID )
Jacco Schalkwijk
SQL Server MVP
"Chubby Arse" <alexstevens@.gcc.co.uk> wrote in message
news:1116238813.680762.296230@.g44g2000cwa.googlegr oups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>
|||It's *probably* slow bevause there's no index on the FKs in the
tblVisitTechnician table.
Use the two FKs (VsiitRef, TechnicianRef) as composite Primary Key (You
probably don' need VisitTechnicianID).
1/. This will constrain table from having multiple records for same visit
and same Technician (Can one technician be included on same visit twice?
2. It will also improve query performance when querying on VisitRef.
3. If yo also query tblVisitTechnician table on TechnicianRef alone, add
anothe non-clustered index on TechnicianRef aline
"Chubby Arse" wrote:

> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>

Best way to index my simple join table:

Hi,
I was hoping that someone would give me advice on the best way to index
my tables, for this situtation.
I have two tables, tblVisit and tblTechnician.
tblVisit has this primary key
VisitID int Identity(1,1)
tblTechnician has this primary key:
TechnicianID int Identity(1,1)
They are joined by a third table tblVisitTechnician which links the
two, enabling a visit to have more than one technician allocated:
tblVisitTechnician is structured:
VisitTechnicianID int Identity(1,1) - PrimaryKey
VisitRef int
TechnicianRef int
and both VisitRef and TechnicianRef are both setup as foriegn keys.
The problem I have is that any query I run which uses this join tbale
seems to get slowed down significantly.
What is the best to index these tables, to get the best performance?
Thanks
Alex StevensThat not that much information for suggesting something to you... What does
the query plan show you ?
To extract the query plan for a query, issue the follwing statement in QA:
SET SHOWPLAN_TEXT ON
<Youqruery>
SET SHOWPLAN_TEXT OFF
Like that:
USE NORTHWIND
GO
SET SHOWPLAN_ALL ON
GO
Select * from Orders
GO
SET SHOWPLAN_ALL OFF
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chubby Arse" <alexstevens@.gcc.co.uk> schrieb im Newsbeitrag
news:1116238813.680762.296230@.g44g2000cwa.googlegroups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>|||I don't think you need the VisitTechnicianID column, (VisitRef,
TechnicianRef) is the real primary key of this table, and I doubt that
adding an identity column to a table like this adds any value. As you
probably query on both Visit and Technician regularly, I would do it as
follows (using my preferred naming conventions):
CREATE TABLE VisitsTechnicians (
VisitID int NOT NULL,
TechnicianID int NOT NULL,
CONSTRAINT PK_VisitsTechnicians PRIMARY KEY (VisitID, TechnicianID),
CONSTRAINT FK_VisitsTechnicians__tblVisit
FOREIGN KEY (VisitID )
REFEENCES tblVisit (VisitID ),
CONSTRAINT FK_VisitsTechnicians__tblTechnician
FOREIGN KEY (TechnicianID )
REFEENCES tblVisit (TechnicianID )
)
CREATE NONCLUSTERED INDEX ix_VisitsTechnicians__TechnicianID__Visi
tID
ON VisitsTechnicians (VisitID , TechnicianID )
Jacco Schalkwijk
SQL Server MVP
"Chubby Arse" <alexstevens@.gcc.co.uk> wrote in message
news:1116238813.680762.296230@.g44g2000cwa.googlegroups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>|||It's *probably* slow bevause there's no index on the FKs in the
tblVisitTechnician table.
Use the two FKs (VsiitRef, TechnicianRef) as composite Primary Key (You
probably don' need VisitTechnicianID).
1/. This will constrain table from having multiple records for same visit
and same Technician (Can one technician be included on same visit twice?
2. It will also improve query performance when querying on VisitRef.
3. If yo also query tblVisitTechnician table on TechnicianRef alone, add
anothe non-clustered index on TechnicianRef aline
"Chubby Arse" wrote:

> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>

Best way to index my simple join table:

Hi,
I was hoping that someone would give me advice on the best way to index
my tables, for this situtation.
I have two tables, tblVisit and tblTechnician.
tblVisit has this primary key
VisitID int Identity(1,1)
tblTechnician has this primary key:
TechnicianID int Identity(1,1)
They are joined by a third table tblVisitTechnician which links the
two, enabling a visit to have more than one technician allocated:
tblVisitTechnician is structured:
VisitTechnicianID int Identity(1,1) - PrimaryKey
VisitRef int
TechnicianRef int
and both VisitRef and TechnicianRef are both setup as foriegn keys.
The problem I have is that any query I run which uses this join tbale
seems to get slowed down significantly.
What is the best to index these tables, to get the best performance?
Thanks
Alex StevensThat not that much information for suggesting something to you... What does
the query plan show you ?
To extract the query plan for a query, issue the follwing statement in QA:
SET SHOWPLAN_TEXT ON
<Youqruery>
SET SHOWPLAN_TEXT OFF
Like that:
USE NORTHWIND
GO
SET SHOWPLAN_ALL ON
GO
Select * from Orders
GO
SET SHOWPLAN_ALL OFF
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Chubby Arse" <alexstevens@.gcc.co.uk> schrieb im Newsbeitrag
news:1116238813.680762.296230@.g44g2000cwa.googlegroups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>|||I don't think you need the VisitTechnicianID column, (VisitRef,
TechnicianRef) is the real primary key of this table, and I doubt that
adding an identity column to a table like this adds any value. As you
probably query on both Visit and Technician regularly, I would do it as
follows (using my preferred naming conventions):
CREATE TABLE VisitsTechnicians (
VisitID int NOT NULL,
TechnicianID int NOT NULL,
CONSTRAINT PK_VisitsTechnicians PRIMARY KEY (VisitID, TechnicianID),
CONSTRAINT FK_VisitsTechnicians__tblVisit
FOREIGN KEY (VisitID )
REFEENCES tblVisit (VisitID ),
CONSTRAINT FK_VisitsTechnicians__tblTechnician
FOREIGN KEY (TechnicianID )
REFEENCES tblVisit (TechnicianID )
)
CREATE NONCLUSTERED INDEX ix_VisitsTechnicians__TechnicianID__Visi
tID
ON VisitsTechnicians (VisitID , TechnicianID )
Jacco Schalkwijk
SQL Server MVP
"Chubby Arse" <alexstevens@.gcc.co.uk> wrote in message
news:1116238813.680762.296230@.g44g2000cwa.googlegroups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>|||It's *probably* slow bevause there's no index on the FKs in the
tblVisitTechnician table.
Use the two FKs (VsiitRef, TechnicianRef) as composite Primary Key (You
probably don' need VisitTechnicianID).
1/. This will constrain table from having multiple records for same visit
and same Technician (Can one technician be included on same visit twice?
2. It will also improve query performance when querying on VisitRef.
3. If yo also query tblVisitTechnician table on TechnicianRef alone, add
anothe non-clustered index on TechnicianRef aline
"Chubby Arse" wrote:

> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>

Best way to index my simple join table:

Hi,
I was hoping that someone would give me advice on the best way to index
my tables, for this situtation.
I have two tables, tblVisit and tblTechnician.
tblVisit has this primary key
VisitID int Identity(1,1)
tblTechnician has this primary key:
TechnicianID int Identity(1,1)
They are joined by a third table tblVisitTechnician which links the
two, enabling a visit to have more than one technician allocated:
tblVisitTechnician is structured:
VisitTechnicianID int Identity(1,1) - PrimaryKey
VisitRef int
TechnicianRef int
and both VisitRef and TechnicianRef are both setup as foriegn keys.
The problem I have is that any query I run which uses this join tbale
seems to get slowed down significantly.
What is the best to index these tables, to get the best performance?
Thanks
Alex StevensThat not that much information for suggesting something to you... What does
the query plan show you ?
To extract the query plan for a query, issue the follwing statement in QA:
SET SHOWPLAN_TEXT ON
<Youqruery>
SET SHOWPLAN_TEXT OFF
Like that:
USE NORTHWIND
GO
SET SHOWPLAN_ALL ON
GO
Select * from Orders
GO
SET SHOWPLAN_ALL OFF
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Chubby Arse" <alexstevens@.gcc.co.uk> schrieb im Newsbeitrag
news:1116238813.680762.296230@.g44g2000cwa.googlegroups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>|||I don't think you need the VisitTechnicianID column, (VisitRef,
TechnicianRef) is the real primary key of this table, and I doubt that
adding an identity column to a table like this adds any value. As you
probably query on both Visit and Technician regularly, I would do it as
follows (using my preferred naming conventions):
CREATE TABLE VisitsTechnicians (
VisitID int NOT NULL,
TechnicianID int NOT NULL,
CONSTRAINT PK_VisitsTechnicians PRIMARY KEY (VisitID, TechnicianID),
CONSTRAINT FK_VisitsTechnicians__tblVisit
FOREIGN KEY (VisitID )
REFEENCES tblVisit (VisitID ),
CONSTRAINT FK_VisitsTechnicians__tblTechnician
FOREIGN KEY (TechnicianID )
REFEENCES tblVisit (TechnicianID )
)
CREATE NONCLUSTERED INDEX ix_VisitsTechnicians__TechnicianID__VisitID
ON VisitsTechnicians (VisitID , TechnicianID )
--
Jacco Schalkwijk
SQL Server MVP
"Chubby Arse" <alexstevens@.gcc.co.uk> wrote in message
news:1116238813.680762.296230@.g44g2000cwa.googlegroups.com...
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>|||It's *probably* slow bevause there's no index on the FKs in the
tblVisitTechnician table.
Use the two FKs (VsiitRef, TechnicianRef) as composite Primary Key (You
probably don' need VisitTechnicianID).
1/. This will constrain table from having multiple records for same visit
and same Technician (Can one technician be included on same visit twice?
2. It will also improve query performance when querying on VisitRef.
3. If yo also query tblVisitTechnician table on TechnicianRef alone, add
anothe non-clustered index on TechnicianRef aline
"Chubby Arse" wrote:
> Hi,
> I was hoping that someone would give me advice on the best way to index
> my tables, for this situtation.
> I have two tables, tblVisit and tblTechnician.
> tblVisit has this primary key
> VisitID int Identity(1,1)
> tblTechnician has this primary key:
> TechnicianID int Identity(1,1)
> They are joined by a third table tblVisitTechnician which links the
> two, enabling a visit to have more than one technician allocated:
> tblVisitTechnician is structured:
> VisitTechnicianID int Identity(1,1) - PrimaryKey
> VisitRef int
> TechnicianRef int
> and both VisitRef and TechnicianRef are both setup as foriegn keys.
> The problem I have is that any query I run which uses this join tbale
> seems to get slowed down significantly.
> What is the best to index these tables, to get the best performance?
> Thanks
> Alex Stevens
>

Best Way of data transfer

Hello All,
I am a newbie dba and need some expert advice on one of my development
scenario I am currently struck with. I am in process of designing a
reporting solution for my company. It is going to be a web based
intranet thin client multitiered application using sql server 2000 and
..net platform.
This application basically shows the real time KPI's or statistics in
different forms of reports on an hourly basis to the senior managers to
right on their desktop. Eventually these reports will help them in
decision making for the better performance of the business...
My question here is what is the best way of transferring large chunk of
data (not entire table(s)) from production server (SQL 2000) to a
reporting server or staging database with an hourly refresh without
stressing the production environment?
Is it a) Replication preferably snapshot? Or
b) BCP? or
c) DTS?
Or do you suggest any better way of achieving this task?
Any suggestion or tips would be greatly appreciated.
Looking forward for your responses.
Many Thanks,
AK
I'd recommend using transactional replication - after the snapshot, it'll
just take the changes to the data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks very much for your reply. Paul,
Is it viable to do hourly refresh through out the day using your
advised approach? Will there be any performance issues?
Thanks very much for your reply.
Ak
Paul Ibison wrote:
> I'd recommend using transactional replication - after the snapshot, it'll
> just take the changes to the data.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The log-reader will ov course affect the production system, and if you have
a publisher/distributor, there will be disk access required to write and
read from the distribution database. Exactly what this all amounts to is
difficult to say and is more empirically determined, although the section
entitled "Cost of Transactional Replication at the Publisher" in this
article will give you some idea:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.
The other thing to take into account is the effect on reporting queries to
have the distribution agent aplying transactions, and the consequential
potential blocking issues.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||I would use transactional replication as it offers the lowest latency.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AK" <arshad.khan@.policyadmin.co.uk> wrote in message
news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...
> Hello All,
> I am a newbie dba and need some expert advice on one of my development
> scenario I am currently struck with. I am in process of designing a
> reporting solution for my company. It is going to be a web based
> intranet thin client multitiered application using sql server 2000 and
> .net platform.
> This application basically shows the real time KPI's or statistics in
> different forms of reports on an hourly basis to the senior managers to
> right on their desktop. Eventually these reports will help them in
> decision making for the better performance of the business...
>
> My question here is what is the best way of transferring large chunk of
> data (not entire table(s)) from production server (SQL 2000) to a
> reporting server or staging database with an hourly refresh without
> stressing the production environment?
> Is it a) Replication preferably snapshot? Or
> b) BCP? or
> c) DTS?
> Or do you suggest any better way of achieving this task?
> Any suggestion or tips would be greatly appreciated.
> Looking forward for your responses.
>
> Many Thanks,
> AK
>
|||Thanks very much for all your replies guys.
I will give at a go this way then. Will post more queries on this
thread if i get stuck any where.
AK
Hilary Cotter wrote:[vbcol=seagreen]
> I would use transactional replication as it offers the lowest latency.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "AK" <arshad.khan@.policyadmin.co.uk> wrote in message
> news:1163424096.157922.228010@.k70g2000cwa.googlegr oups.com...