Showing posts with label below. Show all posts
Showing posts with label below. Show all posts

Friday, March 23, 2012

Indirect configurations ROCK!

Hi Jamie,

You wrote In your blog which I pasted below:

"I have built my packages in such a way that different packages can use the same configuration file. For example I have a "Master" configuration file that stores information that all of my packages will need (e.g. connection strings for my log files, warehouse database and metadata database). Our project makes use of many source systems so I also have a configuration file for each source system as well - that way every package that accesses a certain source system can use the appropriate configuration file. .."

How do you create a configuration for each source? I thought the concept of environmental variable is to have one configuration file in a location and defined the path to it in your system environmental variable value?

I have only one config file file for all my 9 packages and I have been having problems.

Maybe I am doing something wrong but what? Can you be more specific about this great idirect configuration?

Thanks

Omon

Putting multiple values in one configuration file is fine if you always have all targets in all packages to which you apply the configuration file to. This means if you have 5 connection strings in a configuration file, then all packages for which you use that file, must have all 5 connections. Very often this is not the case. Normally you would have x connections, but of that maybe one or two are used in every package, the rest are only used in one or two packages. So I suggest you have one configuration file for the one or two common connections, and then one file per additional connection. This gives flexibility for you to pick and choose the configuration files you require per package.

You then need one environment variable per configuration file. That is what Jamie and I use.

|||

Interesting. This might solve my nightmare. I am exploring this senerio now because I have 6 connections in total but all mt packages are using 2 or 3 connections each. I don't have all my 6 connections in all my packages. I am going to add the rest connections to my packages and see what happened.

Thanks

Omon.

Monday, March 19, 2012

Indexing on calculated fields

I would like to index on a calculated field. I need to index records by
w and want to store the W Number. Below is a test schema that
should work but the index will not create.
drop table calcdate;
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,[basedate],0)
);
create index calcdate_idx ON calcdate(calcsun);
I get the error " 37000(1933)[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot create index because the key column 'calcsun' is
non-deterministic or imprecise."
The page
http://msdn.microsoft.com/library/d...>
_08_95v7.asp
says that DATEDIFF is deterministic so I cannot see why the above index
will not create.
All help much appreciated.
GJHello, GJ
I was somehow surprised of this behaviour, too. It turns out that
DATEDIFF is indeed deterministic, but of it's parameters was not: 0 as
a datetime is non-deterministic! The page you quoted says that CONVERT
is deterministic with a datetime only when the style parameter is
specified (and it's not 0, 9, 100 or 109). Therefore, this works (and I
think that you will get the same results):
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,basedate,convert(datetime,'1
9000101',112))
);
create index calcdate_idx ON calcdate(calcsun);
Razvan

Friday, March 9, 2012

INDEXES: Is this correct

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?> All I want to know is would this code work?
Well, did you try it? What happened?
--
http://www.aspfaq.com/
(Reverse address to reply.)

Wednesday, March 7, 2012

Indexes confusion

I have a temp table and created two indexes on it.
Please See below
--
CREATE TABLE #Invoice (
InvoiceID int NOT NULL,
CustomerKey varchar(20),
DebtorId int,
Reference varchar(50),
BalanceDue money,
DebtID int,
Reason varchar(100),
DebtStatus int,
)
CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
---
I use DebtID as index because I use it again as a inner join to this table
in another query. So I assume it will make much faster. All I want to know is
would this code work?Already answered, please see your other thread, and these articles:
http://www.aspfaq.com/5007
http://www.aspfaq.com/5003
--
http://www.aspfaq.com/
(Reverse address to reply.)
"raj" <raj@.discussions.microsoft.com> wrote in message
news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> I have a temp table and created two indexes on it.
> Please See below
> --
> CREATE TABLE #Invoice (
> InvoiceID int NOT NULL,
> CustomerKey varchar(20),
> DebtorId int,
> Reference varchar(50),
> BalanceDue money,
> DebtID int,
> Reason varchar(100),
> DebtStatus int,
> )
> CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> ---
> I use DebtID as index because I use it again as a inner join to this table
> in another query. So I assume it will make much faster. All I want to know
is
> would this code work?
>|||I'm sorry , but I couldn't find the answer in the newsgroup.
"Aaron [SQL Server MVP]" wrote:
> Already answered, please see your other thread, and these articles:
> http://www.aspfaq.com/5007
> http://www.aspfaq.com/5003
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> > I have a temp table and created two indexes on it.
> > Please See below
> > --
> > CREATE TABLE #Invoice (
> > InvoiceID int NOT NULL,
> > CustomerKey varchar(20),
> > DebtorId int,
> > Reference varchar(50),
> > BalanceDue money,
> > DebtID int,
> > Reason varchar(100),
> > DebtStatus int,
> > )
> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> > ---
> >
> > I use DebtID as index because I use it again as a inner join to this table
> > in another query. So I assume it will make much faster. All I want to know
> is
> > would this code work?
> >
> >
>
>|||It should work fine. But bear in mind you're creating a temporary table
(with the # prefix on the tablename) so it will get created in tempdb and
will be automatically dropped when it goes out of scope (most likely at the
end of your batch). I don't know how much faster the indexes will really
make your queries - the best way to find out is trial and error (set a
couple session variables ("set statistics io on" and "set statistics time
on") at the beginning of your batch and turn on the execution plans to see
what real difference it makes).
Most temp table solutions don't benefit too much from indexes (since they're
usually pretty small tables, they're very short lived anyway and it takes
some resources to maintain the indexes). Both indexes you create in your
script are nonclustered which means your data itself is unsorted (it's a
"heap"). You might benefit from creating a clustered index on your table on
a suitable column, but as I just mentioned it'll probably take more
resources to maintain these indexes than it would to query the table without
the indexes in the first place. Trial & error is the only way to tell.
--
Cheers,
Mike
"raj" <raj@.discussions.microsoft.com> wrote in message
news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> I'm sorry , but I couldn't find the answer in the newsgroup.
> "Aaron [SQL Server MVP]" wrote:
>> Already answered, please see your other thread, and these articles:
>> http://www.aspfaq.com/5007
>> http://www.aspfaq.com/5003
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "raj" <raj@.discussions.microsoft.com> wrote in message
>> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
>> > I have a temp table and created two indexes on it.
>> > Please See below
>> > --
>> > CREATE TABLE #Invoice (
>> > InvoiceID int NOT NULL,
>> > CustomerKey varchar(20),
>> > DebtorId int,
>> > Reference varchar(50),
>> > BalanceDue money,
>> > DebtID int,
>> > Reason varchar(100),
>> > DebtStatus int,
>> > )
>> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
>> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
>> > ---
>> >
>> > I use DebtID as index because I use it again as a inner join to this
>> > table
>> > in another query. So I assume it will make much faster. All I want to
>> > know
>> is
>> > would this code work?
>> >
>> >
>>|||Thank you very much for your help. Greatly appreciate.
raj
"Mike Hodgson" wrote:
> It should work fine. But bear in mind you're creating a temporary table
> (with the # prefix on the tablename) so it will get created in tempdb and
> will be automatically dropped when it goes out of scope (most likely at the
> end of your batch). I don't know how much faster the indexes will really
> make your queries - the best way to find out is trial and error (set a
> couple session variables ("set statistics io on" and "set statistics time
> on") at the beginning of your batch and turn on the execution plans to see
> what real difference it makes).
> Most temp table solutions don't benefit too much from indexes (since they're
> usually pretty small tables, they're very short lived anyway and it takes
> some resources to maintain the indexes). Both indexes you create in your
> script are nonclustered which means your data itself is unsorted (it's a
> "heap"). You might benefit from creating a clustered index on your table on
> a suitable column, but as I just mentioned it'll probably take more
> resources to maintain these indexes than it would to query the table without
> the indexes in the first place. Trial & error is the only way to tell.
> --
> Cheers,
> Mike
> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:86C3A5FC-2F1E-4717-9130-7D372FB1B66E@.microsoft.com...
> > I'm sorry , but I couldn't find the answer in the newsgroup.
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >> Already answered, please see your other thread, and these articles:
> >>
> >> http://www.aspfaq.com/5007
> >> http://www.aspfaq.com/5003
> >>
> >> --
> >> http://www.aspfaq.com/
> >> (Reverse address to reply.)
> >>
> >>
> >>
> >>
> >> "raj" <raj@.discussions.microsoft.com> wrote in message
> >> news:0C4B1D69-3A7F-4202-B2DD-059127C6175C@.microsoft.com...
> >> > I have a temp table and created two indexes on it.
> >> > Please See below
> >> > --
> >> > CREATE TABLE #Invoice (
> >> > InvoiceID int NOT NULL,
> >> > CustomerKey varchar(20),
> >> > DebtorId int,
> >> > Reference varchar(50),
> >> > BalanceDue money,
> >> > DebtID int,
> >> > Reason varchar(100),
> >> > DebtStatus int,
> >> > )
> >> > CREATE INDEX idxInvoice ON #Invoice (InvoiceID)
> >> > CREATE INDEX idxInvoice2 ON #Invoice (DebtID)
> >> > ---
> >> >
> >> > I use DebtID as index because I use it again as a inner join to this
> >> > table
> >> > in another query. So I assume it will make much faster. All I want to
> >> > know
> >> is
> >> > would this code work?
> >> >
> >> >
> >>
> >>
> >>
>
>

Friday, February 24, 2012

Indexes

Hi - Can anyone tell me the best approach to indexing a database I am using
for reporting?
I have a data table & a time table(below), the time table has a timeID field
with an incrementing integer ID & a clustered Index on this field.
Each time record represents a unique reporting time stamp with all time
details such as day of week, monthname, day of month etc... as other fields.
The data table joins to the time table with the timeID as a foreign key in
the data table.
I am wondering whether I should drop the TimeID field on the time table &
instead use the "localtime" field as the clustered index instead since all
localtime values will be unique (& remove the timeID foreign key from the
data table & replace it with a localtime field).
Would this be more efficient than an integer key as I am likely to query
data by time ranges' table definitions below.
Thanks for any advice on this!!
Mike Knee
CREATE TABLE [dbo].[Data] (
[DataID] [int] IDENTITY (1, 1) NOT NULL ,
[HeaderID] [int] NOT NULL ,
[Value] [float] NOT NULL ,
[TimeID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DimTime] (
[TimeID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeLocal] [smalldatetime] NOT NULL ,
[Year] [smallint] NULL ,
[MonthName] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[MonthNumber] [tinyint] NULL ,
[DayName] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[Quarter] [tinyint] NULL ,
[DayOfWeek] [tinyint] NULL ,
[DayOfMonth] [tinyint] NULL ,
[DayOfYear] [smallint] NULL ,
[WeekOfYear] [tinyint] NULL ,
[Hour] [tinyint] NULL ,
[IsHoliday] [tinyint] NULL ,
[IsWeekday] [tinyint] NULL ,
[IsWorkingDay] [tinyint] NULL ,
[Date] [smalldatetime] NULL
) ON [PRIMARY]
GOOn Thu, 2 Nov 2006 12:56:15 -0000, Michael Knee wrote:

>Hi - Can anyone tell me the best approach to indexing a database I am using
>for reporting?
(snip)
>I am wondering whether I should drop the TimeID field on the time table &
>instead use the "localtime" field as the clustered index instead since all
>localtime values will be unique (& remove the timeID foreign key from the
>data table & replace it with a localtime field).
>Would this be more efficient than an integer key as I am likely to query
>data by time ranges' table definitions below.
Hi Michael,
Based on the infoprmation given: yes.
But the best way to find out is to perform tests. On your hardware, with
your queries, and against your data.
Hugo Kornelis, SQL Server MVP

Indexes

Hi - Can anyone tell me the best approach to indexing a database I am using
for reporting?
I have a data table & a time table(below), the time table has a timeID field
with an incrementing integer ID & a clustered Index on this field.
Each time record represents a unique reporting time stamp with all time
details such as day of week, monthname, day of month etc... as other fields.
The data table joins to the time table with the timeID as a foreign key in
the data table.
I am wondering whether I should drop the TimeID field on the time table &
instead use the "localtime" field as the clustered index instead since all
localtime values will be unique (& remove the timeID foreign key from the
data table & replace it with a localtime field).
Would this be more efficient than an integer key as I am likely to query
data by time ranges' table definitions below.
Thanks for any advice on this!!
Mike Knee
CREATE TABLE [dbo].[Data] (
[DataID] [int] IDENTITY (1, 1) NOT NULL ,
[HeaderID] [int] NOT NULL ,
[Value] [float] NOT NULL ,
[TimeID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[DimTime] (
[TimeID] [int] IDENTITY (1, 1) NOT NULL ,
[TimeLocal] [smalldatetime] NOT NULL ,
[Year] [smallint] NULL ,
[MonthName] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[MonthNumber] [tinyint] NULL ,
[DayName] [varchar] (9) COLLATE Latin1_General_CI_AS NULL ,
[Quarter] [tinyint] NULL ,
[DayOfWeek] [tinyint] NULL ,
[DayOfMonth] [tinyint] NULL ,
[DayOfYear] [smallint] NULL ,
[WeekOfYear] [tinyint] NULL ,
[Hour] [tinyint] NULL ,
[IsHoliday] [tinyint] NULL ,
[IsWeekday] [tinyint] NULL ,
[IsWorkingDay] [tinyint] NULL ,
[Date] [smalldatetime] NULL
) ON [PRIMARY]
GOOn Thu, 2 Nov 2006 12:56:15 -0000, Michael Knee wrote:
>Hi - Can anyone tell me the best approach to indexing a database I am using
>for reporting?
(snip)
>I am wondering whether I should drop the TimeID field on the time table &
>instead use the "localtime" field as the clustered index instead since all
>localtime values will be unique (& remove the timeID foreign key from the
>data table & replace it with a localtime field).
>Would this be more efficient than an integer key as I am likely to query
>data by time ranges' table definitions below.
Hi Michael,
Based on the infoprmation given: yes.
But the best way to find out is to perform tests. On your hardware, with
your queries, and against your data.
--
Hugo Kornelis, SQL Server MVP