Any SQL Server Guru?

Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny1) Create a separate DB for the functions and procs.
2) Use 3-part naming for the objects they access.
3) Create a separate DB with data only. Refresh just that DB. The
objects referred to in #2 are in this DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
Dear SQL Server Gurus:
Your input and advises are greatly appreciated!
We have a SQL Server 2005 database served for Decision Support and Reporting
purposes and it is about 15 G and growing moderately. The source data is
from DB2, an on line transactional database. Everyday, data (existing or
modified tables with data) was transformed into a SQL server staging
database
and got backup on that server. Then the backup was ftp to our produciton
server and the restore process was kick off to restore the database. During
that restore process, our production database got refreshed, however, all
views/functions/store procedures created and edited by the developer will be
gone too. To resolve this problem, everyday our developer before going
home,
she would generate all scripts (including views/functions/procedures) thru
Server Management studio and run it thru command line from a job scheduler
right after the restore process was done. We have over 300 views, functions
and store procedures. When the developer generates the scripts, she needs
to manually reset value on the Server Management Studio and it is pretty
risky, for if she did not reset value right (for instance, set dependency to
true), she will not get the correct scripts and the restore process will
wipe
out the scripts and the reporting programs would fail. I am looking for a
way to streamline the developer's working procedures.
Is there any way to keep the functions/views/procedure stored on the
database when the tables and data got refreshed? or do you have any other
recommendation?
Thanks,
Jenny|||Sounds like best option is to create another database for all of the
reporting stored procedures/functions/views. It will take a little bit of
work to go through all of the objects and change the object name to indicate
the database where the data is located. (Check Books Online for "External
Data and Transact-SQL".)
But the end result is that you 'refresh' process would not 'wipe out' the
objects.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too.
Rather that generate scripts, consider keeping the 'master' scripts under
source control (or at least in the file system). You can then run the
scripts as part of a post-restore process. This is much less risky than
using the database as the source and generating scripts. Keeping scripts
under source control is a Best Practice in SQL Server development.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>|||3-part naming of objects is of the form:
SQL 2000: Database.Owner.ObjectName
SQL 2005: Database.Schema.ObjectName
Thus, you would refer to a table in the MyDB database as:
MyDB.dbo.MyTable
You would have 2 DB's, say, DBFunc and DBData. Place your stored procs and
functions in DBFunc. Place your data in DBData. In the functions/procs,
refer to your tables in the form shown above.
HTH
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Jenny" <Jenny@.discussions.microsoft.com> wrote in message
news:3F2249E6-86F6-454A-9DCA-1C4F194348D4@.microsoft.com...
Hi Tom,
What do you mean in item 2 and 3?
I created a backup db and truncate data, which would fullfilled the first
comments you mentioned. Then what is next? I don't quite understand what
you said on item 2 & 3.
Thanks,
"Tom Moreau" wrote:
> 1) Create a separate DB for the functions and procs.
> 2) Use 3-part naming for the objects they access.
> 3) Create a separate DB with data only. Refresh just that DB. The
> objects referred to in #2 are in this DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Jenny" <Jenny@.discussions.microsoft.com> wrote in message
> news:63BCA4BD-6889-4157-81BB-AC6B510AEFDB@.microsoft.com...
> Dear SQL Server Gurus:
> Your input and advises are greatly appreciated!
> We have a SQL Server 2005 database served for Decision Support and
> Reporting
> purposes and it is about 15 G and growing moderately. The source data is
> from DB2, an on line transactional database. Everyday, data (existing or
> modified tables with data) was transformed into a SQL server staging
> database
> and got backup on that server. Then the backup was ftp to our produciton
> server and the restore process was kick off to restore the database.
> During
> that restore process, our production database got refreshed, however, all
> views/functions/store procedures created and edited by the developer will
> be
> gone too. To resolve this problem, everyday our developer before going
> home,
> she would generate all scripts (including views/functions/procedures) thru
> Server Management studio and run it thru command line from a job scheduler
> right after the restore process was done. We have over 300 views,
> functions
> and store procedures. When the developer generates the scripts, she
> needs
> to manually reset value on the Server Management Studio and it is pretty
> risky, for if she did not reset value right (for instance, set dependency
> to
> true), she will not get the correct scripts and the restore process will
> wipe
> out the scripts and the reporting programs would fail. I am looking for a
> way to streamline the developer's working procedures.
> Is there any way to keep the functions/views/procedure stored on the
> database when the tables and data got refreshed? or do you have any other
> recommendation?
>
> Thanks,
>
> Jenny
>

Any clue to make this query run faster

Any help would be really appreciated...
My stored procedure...

CREATE PROCEDURE business3rd7
@.Fromdate DATETIME,
@.ToDate DATETIME
AS

select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVE RT(VARCHAR(10),Receipts.Companynumber2) as co ,
Receipts.Premium1+Receipts.Premium2 as Premium,
"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,

"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,

---
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) end,

"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) else 0 end end,

"Current Year"= case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @.Fromdate)
AND DateAdd(year, -1, @.ToDate) then count(clubamount) end,
"#AA"=count(receipts.clubamount),
"$AA"=sum(receipts.clubamount)

FROM Receipts,Policy
where Receipts.Agencyid=Policy.Agentid
group by
Receipts.CompanyNumber1,Receipts.CompanyNumber2,
Receipts.Premium1,Receipts.Premium2,
Receipts.TransactionType,policy.Renewalofferdate,
Receipts.Agencyid

GO

Query plan...

-------Query Plan

|--Sort(DISTINCT ORDER BY:([Expr1008] ASC, [Expr1009] ASC, [Expr1010] ASC, [Expr1011] ASC, [Expr1012] ASC, [Expr1013] ASC, [Expr1014] ASC, [Expr1015] ASC, [Expr1016] ASC, [Expr1017] ASC, [Expr1018] ASC, [Expr1019] ASC, [Expr1020] ASC, [Expr1021] ASC, [Ex
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Receipts].[CompanyNumber1])+Convert([Receipts].[CompanyNumber2]), [Expr1009]=[Receipts].[Premium1]+[Receipts].[Premium2], [Expr1010]=If ([Receipts].[TransactionType]='CAN') then ([Receipts].[Premium1]+[R
|--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1076]), [Expr1005]=Convert([Expr1077]), [Expr1006]=Convert([Expr1078]), [Expr1007]=If ([Expr1078]=0) then NULL else [Expr1079]))
|--Stream Aggregate(GROUP BY:([Receipts].[CompanyNumber1], [Receipts].[CompanyNumber2], [Receipts].[Premium1], [Receipts].[Premium2], [Receipts].[TransactionType], [Policy].[RenewalOfferDate], [Receipts].[AgencyID]) DEFINE:([Expr1076]=COUN
|--Sort(ORDER BY:([Receipts].[CompanyNumber1] ASC, [Receipts].[CompanyNumber2] ASC, [Receipts].[Premium1] ASC, [Receipts].[Premium2] ASC, [Receipts].[TransactionType] ASC, [Policy].[RenewalOfferDate] ASC, [Receipts].[AgencyID] ASC))
|--Hash Match(Inner Join, HASH:([Policy].[AgentID])=([Receipts].[AgencyID]), RESIDUAL:([Policy].[AgentID]=[Receipts].[AgencyID]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Policy]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Receipts]))

\

The two tables has number of records as 13349 and 97032.It taking more than 30 mins...
Any way to make it faster...well distinct sucks.

you have 2 table scans. I always use the graphical execution plan, but if these are big query costs, you may want to index the columns involved.|||well distinct sucks.

you have 2 table scans. I always use the graphical execution plan, but if these are big query costs, you may want to index the columns involved.

Thank you Thrasymachus.My guess was right...yea one of the culprit is that two table scan let me create some index on those columns and I have to think over that distinct again.|||Lol - I didn't even know you could use distinct and group by in a single query. In any case - this makes distinct doubly unnecessary.|||Yea,thats my mistake ..I overlooked that...well after creating the index on the columns not a significant change has resulted.
...:S

Any clue...|||A where clause?|||A where clause?
I didnt get you,I can't use where clause with @.fromdate and @.todate ,bcoz I am doing the calculation of previous year also.
well, I think I have to consider the whole query in a different approach...|||where did you create your indices?|||I mean that it is usually pretty rare to need every record from one table and all related records from another. If you need them all then you need them all but point was - do you?|||I mean that it is usually pretty rare to need every record from one table and all related records from another. If you need them all then you need them all but point was - do you?

No,I don't need all records of one table only those records which satisfy the datefrom clause, and then I want some records of other table which is related to this table by agentid.
Clear?|||where did you create your indices?

I have created the clustered and non clustered index on the columns on both tables which are invloved in this query.ok?|||ummm, are they on the columns causing your scans? are your scans a large percentage of your query cost? how big are the tables involved?|||ummm, are they on the columns causing your scans? are your scans a large percentage of your query cost? how big are the tables involved?

well, I checked my estiimated execution plan and found that the sort is taking the 96% cost...I mean the grouping cost 96%.
Any clue?
The tables are having data of 13349 and 97032 respectively,its a testing databse so few data are there...|||Ok, I've taken some liberties trying to get to what I think you want. Some columns are just plain missing because they made no sense to me. The result set is quite differently arranged in that what was once one row has become many rows based on transaction type. This isn't an "apples to apples" comparison, but I think it does what I think you really want, and will actually be easier for you to use!SELECT
Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.transactiontype
, Count(*) AS c
, Sum(Receipts.premium1 + Receipts.premium2) AS dollars
, Count(Receipts.clubamount) AS "AA#"
, Sum(Receipts.clubamount) AS "AA$"
FROM Receipts
JOIN Policy
ON (policy.agentID = Receipts.AgentID)
WHERE policy.renewalofferdate BETWEEN @.Fromdate AND @.ToDate
GROUP BY Policy.AgentID
, Receipts.Company1
, Receipts.Company2
, Receipts.TransactionType-PatP|||This query still not working ...:(
I am trying all things but for some strange reason its not working...
SEE the modified code which contains the crosstab operations...
The two tables are Policy and Receipts tables...

CREATE TABLE [Policy] (

[PolicyNumber] [nvarchar] (10) ,
[PolicySequence] [smallint] NULL ,
[Name] [nvarchar] (40) ,

--there are many columns here and I am are not mentioning those

[AgentID] [int] NULL ,
[RenewalOfferDate] [smalldatetime] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [Receipts] (
[RecordType] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AgencyID] [int] NULL ,
[ContractNumber] NULL ,
[MonthlyPayment] [money] NULL ,
[PaymentType] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PolicyNumber] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PolicySequence] [smallint] NOT NULL ,
[ReceiptNumber] [bigint] NULL ,
[TransactionType] [nchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InsuredName] [nchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DownPayment] [money] NULL ,
[ClubAmount] [money] NULL ,
[ServiceFee] [money] NULL ,
[MVRFee] [money] NULL ,
[SR22Fee] [money] NULL ,
[AgentInitial] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ProcessedInitial] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QuotedInitial] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Source] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyNumber1] [int] NULL ,
[PolicyNumber1] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Premium1] [money] NULL ,
[CompanyNumber2] [int] NULL ,
[PolicyNumber2] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Premium2] [money] NULL ,
[TotalAmount] [money] NULL ,
[HowPaid] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HowPaidCode] [nchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AmountTendered] [money] NULL ,
[Change] [money] NULL ,
[DateEntered] [datetime] NULL ,
[Void] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VoidDate] [datetime] NULL ,
[VoidBy] [nchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RequestVoidBy] [nchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ReasonVoid] [nchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

Here is the code which ran really fast...

@.Fromdate DATETIME,
@.ToDate DATETIME
set @.fromdate='1/1/2006'
set @.todate='12/1/2006'

SELECT
Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.transactiontype
, Count(*) AS c
, Sum(Receipts.premium1 + Receipts.premium2) AS dollars
, Count(Receipts.clubamount) AS "AA#"
, Sum(Receipts.clubamount) AS "AA$"

FROM Receipts
JOIN Policy
ON (Policy.Agentid=Receipts.Agencyid)
WHERE policy.renewalofferdate BETWEEN @.Fromdate AND @.ToDate
GROUP BY Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.TransactionType

This is the modified code which is very slow one...

declare
@.Fromdate DATETIME,
@.ToDate DATETIME
set @.fromdate='1/1/2006'
set @.todate='12/1/2006'

SELECT
Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.transactiontype
, Count(*) AS c
, Sum(Receipts.premium1 + Receipts.premium2) AS dollars
, Count(Receipts.clubamount) AS "AA#"
, Sum(Receipts.clubamount) AS "AA$",

----- [B]These calculations are needed here

"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,

"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,

---
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) end,

"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(policy.policynumber) else 0 end end,

"Current Year"= case when policy.Renewalofferdate between @.Fromdate AND @.ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @.Fromdate)
AND DateAdd(year, -1, @.ToDate) then count(clubamount) end

----

FROM Receipts
JOIN Policy
ON (Policy.Agentid=Receipts.Agencyid)
WHERE policy.renewalofferdate BETWEEN @.Fromdate AND @.ToDate
GROUP BY Policy.AgentID
, Receipts.CompanyNumber1
, Receipts.CompanyNumber2
, Receipts.TransactionType

--- three(3) more group by added

, Receipts.Premium1
, Receipts.Premium2
, Policy.Renewalofferdate

I have created neccessary Indices on the columns which are involed and are grouped.
I have created an index comprises of all the columns involed in the group except Policy.Renewalofferdate.

Any clue?