Can't beat the dynamic sql

Hi all!
I've put in a lot of work in the following stored proc only to find
that it's slower then the old project's dynamic built query(been
running since 1994, I think). Can anyone see any obvious flaw, I know
it's big, but downsizing it might take away some part that contains
some error that someone might find:) What it retrieves is apartments
based on the user's preferences. Anyways here it is, thanx for any
help:
SELECT
Member.l_MemberID as mem_MemberID,
Member.l_PinCode as mem_PinCode,
Member.s_FirstName as mem_FirstName,
Member.s_LastName as mem_LastName,
Member.s_PersNr as mem_SocSec,
Member.s_Co_Nr as mem_CareOf,
Member.s_Address as mem_Address,
Member.l_ZipCode as mem_ZipCode,
Member.s_City as mem_City,
Member.s_WorkPhone as mem_WorkPhone,
Member.s_Phone as mem_Phone,
Member.s_Cell as mem_Cell,
Member.s_Email as mem_Email,
Member.s_Notes as mem_Notes,
Member.b_Rent_Acknowledge as mem_RentAcknowledge,
estates.L_ESTATEID as est_EstateID,
estates.s_HolderName as est_HolderName,
estates.s_HolderNr as est_HolderNr,
estates.s_HolderPhone as est_HolderPhone,
isnull(estates.n_type,0) as est_Type,
estates.B_ONLYCOMPANIES as est_OnlyCompanies,
Convert(nvarchar, estates.d_rooms) as est_Rooms,
estates.n_BedRooms as est_Bedrooms,
RTRIM(estates.S_KITCHEN) as est_Kitchen,
Estates.D_Area as est_Size,
Convert(nvarchar, estates.d_area) as est_Size,
estates.D_BiArea as est_BiArea,
estates.D_LotSize as est_LotSize,
Estates.N_YearBuilt as est_YearBuilt,
Estates.N_YearRestored as est_YearRestored,
Estates.S_Area as est_Area,
Estates.S_Address as est_Address,
RTRIM(Estates.S_AddressNr) as est_AddressNr,
Estates.L_ZipCode as est_ZipCode,
Estates.S_City as est_City,
Estates.N_Floor as est_Floor,
Estates.N_TotFloors as est_TotalFloors,
isnull(Estates.B_Elevator,0) as est_Elevator,
RTRIM(Estates.S_Furnitures) as est_Furnished,
RTRIM(Estates.S_Terrace) as est_Terrace,
isnull(estates.B_LAUNDRYROOM,0) as est_LaundryRoom,
isnull(estates.B_WASHINGMACHINE,0) as est_WashingMachine,
isnull(estates.B_SHOWER,0) as est_Shower,
isnull(estates.B_BATHTUB,0) as est_BathTub,
isnull(estates.B_DISHWASHER,0) as est_Dishwasher,
isnull(estates.B_PARABOL,0) as est_Parabol,
isnull(estates.B_TV,0) as est_TV,
isnull(estates.B_TILESTOVE,0) as est_TileStove,
isnull(estates.B_VIDEO,0) as est_Video,
isnull(estates.B_COMPUTER,0) as est_Computer,
isnull(estates.B_MICRO,0) as est_Micro,
isnull(estates.B_HOUSEROOM,0) as est_HouseRoom,
isnull(estates.B_OWNENTRANCE,0) as est_OwnEntrance,
isnull(estates.B_OWNTOILET,0) as est_OwnToilet,
isnull(estates.B_BATHROOM,0) as est_Bathroom,
isnull(estates.B_BATHROOMPART,0) as est_BathroomPart,
isnull(estates.B_LIVINGROOM,0) as est_LivingRoom,
isnull(estates.B_INCHEAT,0) as est_IncHeat,
isnull(estates.B_CABLETV,0) as est_CableTV,
isnull(estates.B_INCWATER,0) as est_IncWater,
isnull(estates.B_BROADBAND,0) as est_Broadband,
isnull(estates.B_GARBAGEREM,0) as est_GarbageRem,
isnull(estates.B_PHONE,0) as est_Phone,
isnull(estates.B_INCELEC,0) as est_IncElec,
isnull(estates.B_GAS,0) as est_Gas,
isnull(estates.B_CLOSETRAIN,0) as est_CloseTrain,
isnull(estates.B_CLOSETUBE,0) as est_CloseTube,
isnull(estates.B_CLOSEBUS,0) as est_CloseBus,
isnull(estates.B_CLOSETVAR,0) as est_CloseTvar,
isnull(estates.B_CLOSEDOWNTOWN,0) as est_CloseDowntown,
isnull(estates.B_QUIETAREA,0) as est_QuietArea,
isnull(estates.B_CLOSENATURE,0) as est_CloseNature,
isnull(estates.B_SEAVIEW,0) as est_SeaView,
isnull(estates.B_NOPETS,0) as est_NoPets,
isnull(estates.B_NOSMOKERS,0) as est_NoSmokers,
isnull(estates.B_NOKIDS,0) as est_NoKids,
isnull(estates.B_ONLYWEEKDAYS,0) as est_OnlyWDays,
isnull(estates.B_MAN,0) as est_Man,
isnull(estates.B_WOMAN,0) as est_Woman,
Estates.S_RentAdvance as est_RentAdvance,
Estates.L_Rent as est_Rent,
isnull(Estates.B_Permit,0) as est_Permit,
Estates.S_Landlord as est_Landlord,
Estates.S_Landl_Phone as est_LandlordPhone,
Convert(nvarchar(6), Estates.Dat_From, 12) as est_DatFrom,
Convert(nvarchar(6), Estates.Dat_Until, 12) as est_DatTo,
isnull(Estates.B_MaybeLonger,0) as est_MaybeLonger,
Estates.S_Description as est_Description,
isnull(estates.N_STATE,0) as est_State,
isnull(Estates.B_Printed,0) as est_Printed,
isnull(estates.B_EXCLUSIVE,0) as est_Exclusive,
Estates.L_RegisteredID as est_RegisteredID,
Estates.Dat_Registered as est_DatRegistered,
Estates.L_UpdatedID as est_UpdatedID,
Estates.Dat_Updated as est_DatUpdated,
Estates.Dat_Commit_Printed as est_DatCommitPrinted,
Estates.S_TypeBekr as est_TypeCommit,
Estates.S_Comments as est_Comments,
Estates.L_AuthorizedID as est_AuthorizedID,
Convert(nvarchar(10), Estates.Dat_Show, 120) as est_DatShow,
estates.L_AgentID as est_AgentID
FROM
estates
left join multimedia on multimedia.l_estateid=estates.l_estateid and
isnull(N_INDEX,1)=1
left join member with (nolock) on estates.l_memberid=member.l_memberid
WHERE
isnull(estates.B_FIRSTHAND,0) = 0 AND
isnull(dat_show,getdate()) <= getdate() AND
(ISNULL(MULTIMEDIA.N_INDEX,0) = 0 AND
ISNULL(MULTIMEDIA.N_TYPE,0) = 0) AND
((@.RoomsMin = -1) OR (estates.D_ROOMS >= @.RoomsMin)) AND
((@.RoomsMax = -1) OR (estates.D_ROOMS <= @.RoomsMax)) AND
((@.RentMin = -1) OR (estates.L_RENT >= @.RentMin)) AND
((@.RentMax = -1) OR (estates.L_RENT <= @.RentMax)) AND
((@.SizeMin = -1) OR (estates.D_AREA >= @.SizeMin)) AND
((@.SizeMax = -1) OR (estates.D_AREA <= @.SizeMax)) AND
((@.PeriodMin = -1) OR ((case when estates.dat_until is null then
10000 else datediff(dd, isnull(case when dat_from<getdate() then
getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
end)>= @.PeriodMin)) AND
((@.PeriodMax = -1) OR ((case when estates.dat_until is null then
10000 else datediff(dd, isnull(case when dat_from<getdate() then
getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
end)<= @.PeriodMax)) AND
((@.EstateType = -1) OR (estates.N_TYPE = @.EstateType)) AND
(((@.ZipArea=1) AND( estates.l_zipcode BETWEEN 10000 AND 19999 OR
estates.l_zipcode BETWEEN 76100 AND 76295))OR
((@.ZipArea=2) AND( estates.l_zipcode BETWEEN 40001 AND 54999 OR
estates.l_zipcode BETWEEN 66010 AND 66899))OR
((@.ZipArea=3) AND( estates.l_zipcode BETWEEN 20001 AND 29799))OR
((@.ZipArea=4) AND( estates.l_zipcode BETWEEN 30000 AND 39999 OR
estates.l_zipcode BETWEEN 55000 AND 66090 OR
estates.l_zipcode BETWEEN 66900 AND 76099 OR
estates.l_zipcode BETWEEN 76296 AND 99999))OR
((@.ZipArea=5) AND( estates.l_zipcode BETWEEN 11100 AND 11742 OR
estates.l_zipcode BETWEEN 11744 AND 11759 OR
estates.l_zipcode BETWEEN 11600 AND 11899))OR
((@.ZipArea=6) AND( estates.l_zipcode BETWEEN 12000 AND 12999 OR
estates.l_zipcode BETWEEN 11743 AND 11743 OR
estates.l_zipcode BETWEEN 11760 AND 11799))OR
((@.ZipArea=7) AND( estates.l_zipcode BETWEEN 14100 AND 14799 OR
estates.l_zipcode BETWEEN 13500 AND 13699 OR
estates.l_zipcode BETWEEN 13000 AND 13099))OR
((@.ZipArea=8) AND( estates.l_zipcode BETWEEN 13100 AND 13499))OR
((@.ZipArea=9) AND( estates.l_zipcode BETWEEN 16200 AND 16899))OR
((@.ZipArea=10)AND( estates.l_zipcode BETWEEN 16900 AND 17499))OR
((@.ZipArea=11)AND( estates.l_zipcode BETWEEN 18200 AND 19799))OR
((@.ZipArea=12)AND( estates.l_zipcode BETWEEN 11700 AND 11742 OR
estates.l_zipcode BETWEEN 11744 AND 11759 OR
estates.l_zipcode BETWEEN 11600 AND 11899))OR
((@.ZipArea=13)AND( estates.l_zipcode BETWEEN 15100 AND 15299))OR
((@.ZipArea=14)AND( estates.l_zipcode BETWEEN 16700 AND 16899))OR
((@.ZipArea=15)AND( estates.l_zipcode BETWEEN 16900 AND 17199))OR
((@.ZipArea=16)AND( estates.l_zipcode BETWEEN 17200 AND 17499))OR
((@.ZipArea=17)AND( estates.l_zipcode BETWEEN 17500 AND 17799))OR
((@.ZipArea=18)AND( estates.l_zipcode BETWEEN 19100 AND 19299))OR
((@.ZipArea=19)AND( estates.l_zipcode BETWEEN 76100 AND 76299))) AND
((@.State = -1)OR (estates.N_STATE = @.State)) AND
((@.Furnished = '-1')OR (estates.S_FURNITURES != @.Furnished)) AND
((@.PublDaysMax = -1)OR (DATEDIFF(dd, estates.DAT_SHOW,
getdate())<=@.PublDaysMax)) AND
((@.PrivateCompany = -1)OR (estates.B_ONLYCOMPANIES =
@.PrivateCompany)) AND
((@.Exclusive = -1)OR (estates.B_EXCLUSIVE = @.Exclusive)) AND
((@.Street = '')OR (UPPER(estates.S_ADDRESS) LIKE
(UPPER(@.Street)+'%')))
ORDER BY
CASE WHEN @.Sort = 'est_Area' THEN estates.S_AREA ELSE NULL END,
CASE WHEN @.Sort = 'est_DatShow' THEN estates.dat_Show ELSE NULL END
DESC,
CASE WHEN @.Sort = 'est_State' THEN estates.n_State ELSE NULL END,
CASE WHEN @.Sort = 'est_Type' THEN estates.n_Type ELSE NULL END,
CASE WHEN @.Sort = 'est_Rooms' THEN estates.d_Rooms ELSE NULL END,
CASE WHEN @.Sort = 'est_Size' THEN estates.d_Area ELSE NULL END,
CASE WHEN @.Sort = 'est_Rent' THEN estates.l_Rent ELSE NULL END,
CASE WHEN @.Sort = 'est_DatFrom' THEN estates.dat_From ELSE NULL END,
CASE WHEN @.Sort = 'est_DatTo' THEN estates.dat_until ELSE NULL ENDtonicvodka wrote:
> Hi all!
> I've put in a lot of work in the following stored proc only to find
> that it's slower then the old project's dynamic built query(been
> running since 1994, I think). Can anyone see any obvious flaw, I know
> it's big, but downsizing it might take away some part that contains
> some error that someone might find:) What it retrieves is apartments
> based on the user's preferences. Anyways here it is, thanx for any
> help:
>
> SELECT
> Member.l_MemberID as mem_MemberID,
> Member.l_PinCode as mem_PinCode,
> Member.s_FirstName as mem_FirstName,
> Member.s_LastName as mem_LastName,
> Member.s_PersNr as mem_SocSec,
> Member.s_Co_Nr as mem_CareOf,
> Member.s_Address as mem_Address,
> Member.l_ZipCode as mem_ZipCode,
> Member.s_City as mem_City,
> Member.s_WorkPhone as mem_WorkPhone,
> Member.s_Phone as mem_Phone,
> Member.s_Cell as mem_Cell,
> Member.s_Email as mem_Email,
> Member.s_Notes as mem_Notes,
> Member.b_Rent_Acknowledge as mem_RentAcknowledge,
> estates.L_ESTATEID as est_EstateID,
> estates.s_HolderName as est_HolderName,
> estates.s_HolderNr as est_HolderNr,
> estates.s_HolderPhone as est_HolderPhone,
> isnull(estates.n_type,0) as est_Type,
> estates.B_ONLYCOMPANIES as est_OnlyCompanies,
> Convert(nvarchar, estates.d_rooms) as est_Rooms,
> estates.n_BedRooms as est_Bedrooms,
> RTRIM(estates.S_KITCHEN) as est_Kitchen,
> Estates.D_Area as est_Size,
> Convert(nvarchar, estates.d_area) as est_Size,
> estates.D_BiArea as est_BiArea,
> estates.D_LotSize as est_LotSize,
> Estates.N_YearBuilt as est_YearBuilt,
> Estates.N_YearRestored as est_YearRestored,
> Estates.S_Area as est_Area,
> Estates.S_Address as est_Address,
> RTRIM(Estates.S_AddressNr) as est_AddressNr,
> Estates.L_ZipCode as est_ZipCode,
> Estates.S_City as est_City,
> Estates.N_Floor as est_Floor,
> Estates.N_TotFloors as est_TotalFloors,
> isnull(Estates.B_Elevator,0) as est_Elevator,
> RTRIM(Estates.S_Furnitures) as est_Furnished,
> RTRIM(Estates.S_Terrace) as est_Terrace,
> isnull(estates.B_LAUNDRYROOM,0) as est_LaundryRoom,
> isnull(estates.B_WASHINGMACHINE,0) as est_WashingMachine,
> isnull(estates.B_SHOWER,0) as est_Shower,
> isnull(estates.B_BATHTUB,0) as est_BathTub,
> isnull(estates.B_DISHWASHER,0) as est_Dishwasher,
> isnull(estates.B_PARABOL,0) as est_Parabol,
> isnull(estates.B_TV,0) as est_TV,
> isnull(estates.B_TILESTOVE,0) as est_TileStove,
> isnull(estates.B_VIDEO,0) as est_Video,
> isnull(estates.B_COMPUTER,0) as est_Computer,
> isnull(estates.B_MICRO,0) as est_Micro,
> isnull(estates.B_HOUSEROOM,0) as est_HouseRoom,
> isnull(estates.B_OWNENTRANCE,0) as est_OwnEntrance,
> isnull(estates.B_OWNTOILET,0) as est_OwnToilet,
> isnull(estates.B_BATHROOM,0) as est_Bathroom,
> isnull(estates.B_BATHROOMPART,0) as est_BathroomPart,
> isnull(estates.B_LIVINGROOM,0) as est_LivingRoom,
> isnull(estates.B_INCHEAT,0) as est_IncHeat,
> isnull(estates.B_CABLETV,0) as est_CableTV,
> isnull(estates.B_INCWATER,0) as est_IncWater,
> isnull(estates.B_BROADBAND,0) as est_Broadband,
> isnull(estates.B_GARBAGEREM,0) as est_GarbageRem,
> isnull(estates.B_PHONE,0) as est_Phone,
> isnull(estates.B_INCELEC,0) as est_IncElec,
> isnull(estates.B_GAS,0) as est_Gas,
> isnull(estates.B_CLOSETRAIN,0) as est_CloseTrain,
> isnull(estates.B_CLOSETUBE,0) as est_CloseTube,
> isnull(estates.B_CLOSEBUS,0) as est_CloseBus,
> isnull(estates.B_CLOSETVAR,0) as est_CloseTvar,
> isnull(estates.B_CLOSEDOWNTOWN,0) as est_CloseDowntown,
> isnull(estates.B_QUIETAREA,0) as est_QuietArea,
> isnull(estates.B_CLOSENATURE,0) as est_CloseNature,
> isnull(estates.B_SEAVIEW,0) as est_SeaView,
> isnull(estates.B_NOPETS,0) as est_NoPets,
> isnull(estates.B_NOSMOKERS,0) as est_NoSmokers,
> isnull(estates.B_NOKIDS,0) as est_NoKids,
> isnull(estates.B_ONLYWEEKDAYS,0) as est_OnlyWDays,
> isnull(estates.B_MAN,0) as est_Man,
> isnull(estates.B_WOMAN,0) as est_Woman,
> Estates.S_RentAdvance as est_RentAdvance,
> Estates.L_Rent as est_Rent,
> isnull(Estates.B_Permit,0) as est_Permit,
> Estates.S_Landlord as est_Landlord,
> Estates.S_Landl_Phone as est_LandlordPhone,
> Convert(nvarchar(6), Estates.Dat_From, 12) as est_DatFrom,
> Convert(nvarchar(6), Estates.Dat_Until, 12) as est_DatTo,
> isnull(Estates.B_MaybeLonger,0) as est_MaybeLonger,
> Estates.S_Description as est_Description,
> isnull(estates.N_STATE,0) as est_State,
> isnull(Estates.B_Printed,0) as est_Printed,
> isnull(estates.B_EXCLUSIVE,0) as est_Exclusive,
> Estates.L_RegisteredID as est_RegisteredID,
> Estates.Dat_Registered as est_DatRegistered,
> Estates.L_UpdatedID as est_UpdatedID,
> Estates.Dat_Updated as est_DatUpdated,
> Estates.Dat_Commit_Printed as est_DatCommitPrinted,
> Estates.S_TypeBekr as est_TypeCommit,
> Estates.S_Comments as est_Comments,
> Estates.L_AuthorizedID as est_AuthorizedID,
> Convert(nvarchar(10), Estates.Dat_Show, 120) as est_DatShow,
> estates.L_AgentID as est_AgentID
> FROM
> estates
> left join multimedia on multimedia.l_estateid=estates.l_estateid and
> isnull(N_INDEX,1)=1
> left join member with (nolock) on estates.l_memberid=member.l_memberid
> WHERE
> isnull(estates.B_FIRSTHAND,0) = 0 AND
> isnull(dat_show,getdate()) <= getdate() AND
> (ISNULL(MULTIMEDIA.N_INDEX,0) = 0 AND
> ISNULL(MULTIMEDIA.N_TYPE,0) = 0) AND
> ((@.RoomsMin = -1) OR (estates.D_ROOMS >= @.RoomsMin)) AND
> ((@.RoomsMax = -1) OR (estates.D_ROOMS <= @.RoomsMax)) AND
> ((@.RentMin = -1) OR (estates.L_RENT >= @.RentMin)) AND
> ((@.RentMax = -1) OR (estates.L_RENT <= @.RentMax)) AND
> ((@.SizeMin = -1) OR (estates.D_AREA >= @.SizeMin)) AND
> ((@.SizeMax = -1) OR (estates.D_AREA <= @.SizeMax)) AND
> ((@.PeriodMin = -1) OR ((case when estates.dat_until is null then
> 10000 else datediff(dd, isnull(case when dat_from<getdate() then
> getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
> end)>= @.PeriodMin)) AND
> ((@.PeriodMax = -1) OR ((case when estates.dat_until is null then
> 10000 else datediff(dd, isnull(case when dat_from<getdate() then
> getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
> end)<= @.PeriodMax)) AND
> ((@.EstateType = -1) OR (estates.N_TYPE = @.EstateType)) AND
> (((@.ZipArea=1) AND( estates.l_zipcode BETWEEN 10000 AND 19999 OR
> estates.l_zipcode BETWEEN 76100 AND 76295))OR
> ((@.ZipArea=2) AND( estates.l_zipcode BETWEEN 40001 AND 54999 OR
> estates.l_zipcode BETWEEN 66010 AND 66899))OR
> ((@.ZipArea=3) AND( estates.l_zipcode BETWEEN 20001 AND 29799))OR
> ((@.ZipArea=4) AND( estates.l_zipcode BETWEEN 30000 AND 39999 OR
> estates.l_zipcode BETWEEN 55000 AND 66090 OR
> estates.l_zipcode BETWEEN 66900 AND 76099 OR
> estates.l_zipcode BETWEEN 76296 AND 99999))OR
> ((@.ZipArea=5) AND( estates.l_zipcode BETWEEN 11100 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=6) AND( estates.l_zipcode BETWEEN 12000 AND 12999 OR
> estates.l_zipcode BETWEEN 11743 AND 11743 OR
> estates.l_zipcode BETWEEN 11760 AND 11799))OR
> ((@.ZipArea=7) AND( estates.l_zipcode BETWEEN 14100 AND 14799 OR
> estates.l_zipcode BETWEEN 13500 AND 13699 OR
> estates.l_zipcode BETWEEN 13000 AND 13099))OR
> ((@.ZipArea=8) AND( estates.l_zipcode BETWEEN 13100 AND 13499))OR
> ((@.ZipArea=9) AND( estates.l_zipcode BETWEEN 16200 AND 16899))OR
> ((@.ZipArea=10)AND( estates.l_zipcode BETWEEN 16900 AND 17499))OR
> ((@.ZipArea=11)AND( estates.l_zipcode BETWEEN 18200 AND 19799))OR
> ((@.ZipArea=12)AND( estates.l_zipcode BETWEEN 11700 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=13)AND( estates.l_zipcode BETWEEN 15100 AND 15299))OR
> ((@.ZipArea=14)AND( estates.l_zipcode BETWEEN 16700 AND 16899))OR
> ((@.ZipArea=15)AND( estates.l_zipcode BETWEEN 16900 AND 17199))OR
> ((@.ZipArea=16)AND( estates.l_zipcode BETWEEN 17200 AND 17499))OR
> ((@.ZipArea=17)AND( estates.l_zipcode BETWEEN 17500 AND 17799))OR
> ((@.ZipArea=18)AND( estates.l_zipcode BETWEEN 19100 AND 19299))OR
> ((@.ZipArea=19)AND( estates.l_zipcode BETWEEN 76100 AND 76299))) AND
> ((@.State = -1)OR (estates.N_STATE = @.State)) AND
> ((@.Furnished = '-1')OR (estates.S_FURNITURES != @.Furnished)) AND
> ((@.PublDaysMax = -1)OR (DATEDIFF(dd, estates.DAT_SHOW,
> getdate())<=@.PublDaysMax)) AND
> ((@.PrivateCompany = -1)OR (estates.B_ONLYCOMPANIES =
> @.PrivateCompany)) AND
> ((@.Exclusive = -1)OR (estates.B_EXCLUSIVE = @.Exclusive)) AND
> ((@.Street = '')OR (UPPER(estates.S_ADDRESS) LIKE
> (UPPER(@.Street)+'%')))
> ORDER BY
> CASE WHEN @.Sort = 'est_Area' THEN estates.S_AREA ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatShow' THEN estates.dat_Show ELSE NULL END
> DESC,
> CASE WHEN @.Sort = 'est_State' THEN estates.n_State ELSE NULL END,
> CASE WHEN @.Sort = 'est_Type' THEN estates.n_Type ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rooms' THEN estates.d_Rooms ELSE NULL END,
> CASE WHEN @.Sort = 'est_Size' THEN estates.d_Area ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rent' THEN estates.l_Rent ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatFrom' THEN estates.dat_From ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatTo' THEN estates.dat_until ELSE NULL END
Here are some suggestions.
1. Get rid of all the ORs. For example if you set a default value for
@.roomsmax of 10000 instead of -1 you should be able to just D_ROOMS
BETWEEN @.roomsmin AND @.roomsmax. Same for the other variables.
2. Create a table of zipcodes to join with instead of using CASE
expressions.
3. Pass in date From and To ranges instead of doing the date arithmetic
in the query. You are presently forcing those DATEDIFFs to be performed
for every row.
4. Make some columns non-nullable to eliminate the ISNULLs.
The basic idea is to get expressions in your WHERE clause that are
sargable - that means columns being compared to constants or to other
columns without complex expressions on the columns themselves. Once
you've achieved that you can take best advantage of any indexes you
create.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx|||Dynamic SQL may be faster for something like this, despite
all efforts, but it's worth doing a bit more to see. You need
to balance the security and other risks against the performance.
If you want to improve the performance of the non-dynamic
query, below is another suggestion beyond what David offered.
(You should first look at the query plans and profile
both compilation and execution time to see where the
problems are, then focus on the bottlenecks.)
Precalculate what you can when the data is entered in the first
place. The big one I see is zip code area. Every property
is in some zip code area, so why not store that value in a table?
It would be much better to use estates.l_ziparea = @.ZipArea
than the big mess you have, even if you have to maintain a ziparea
column in a separate table and join to it.
Steve Kass
Drew University
tonicvodka wrote:

>Hi all!
>I've put in a lot of work in the following stored proc only to find
>that it's slower then the old project's dynamic built query(been
>running since 1994, I think). Can anyone see any obvious flaw, I know
>it's big, but downsizing it might take away some part that contains
>some error that someone might find:) What it retrieves is apartments
>based on the user's preferences. Anyways here it is, thanx for any
>help:
>
>SELECT
> Member.l_MemberID as mem_MemberID,
> Member.l_PinCode as mem_PinCode,
> Member.s_FirstName as mem_FirstName,
> Member.s_LastName as mem_LastName,
> Member.s_PersNr as mem_SocSec,
> Member.s_Co_Nr as mem_CareOf,
> Member.s_Address as mem_Address,
> Member.l_ZipCode as mem_ZipCode,
> Member.s_City as mem_City,
> Member.s_WorkPhone as mem_WorkPhone,
> Member.s_Phone as mem_Phone,
> Member.s_Cell as mem_Cell,
> Member.s_Email as mem_Email,
> Member.s_Notes as mem_Notes,
> Member.b_Rent_Acknowledge as mem_RentAcknowledge,
> estates.L_ESTATEID as est_EstateID,
> estates.s_HolderName as est_HolderName,
> estates.s_HolderNr as est_HolderNr,
> estates.s_HolderPhone as est_HolderPhone,
> isnull(estates.n_type,0) as est_Type,
> estates.B_ONLYCOMPANIES as est_OnlyCompanies,
> Convert(nvarchar, estates.d_rooms) as est_Rooms,
> estates.n_BedRooms as est_Bedrooms,
> RTRIM(estates.S_KITCHEN) as est_Kitchen,
> Estates.D_Area as est_Size,
> Convert(nvarchar, estates.d_area) as est_Size,
> estates.D_BiArea as est_BiArea,
> estates.D_LotSize as est_LotSize,
> Estates.N_YearBuilt as est_YearBuilt,
> Estates.N_YearRestored as est_YearRestored,
> Estates.S_Area as est_Area,
> Estates.S_Address as est_Address,
> RTRIM(Estates.S_AddressNr) as est_AddressNr,
> Estates.L_ZipCode as est_ZipCode,
> Estates.S_City as est_City,
> Estates.N_Floor as est_Floor,
> Estates.N_TotFloors as est_TotalFloors,
> isnull(Estates.B_Elevator,0) as est_Elevator,
> RTRIM(Estates.S_Furnitures) as est_Furnished,
> RTRIM(Estates.S_Terrace) as est_Terrace,
> isnull(estates.B_LAUNDRYROOM,0) as est_LaundryRoom,
> isnull(estates.B_WASHINGMACHINE,0) as est_WashingMachine,
> isnull(estates.B_SHOWER,0) as est_Shower,
> isnull(estates.B_BATHTUB,0) as est_BathTub,
> isnull(estates.B_DISHWASHER,0) as est_Dishwasher,
> isnull(estates.B_PARABOL,0) as est_Parabol,
> isnull(estates.B_TV,0) as est_TV,
> isnull(estates.B_TILESTOVE,0) as est_TileStove,
> isnull(estates.B_VIDEO,0) as est_Video,
> isnull(estates.B_COMPUTER,0) as est_Computer,
> isnull(estates.B_MICRO,0) as est_Micro,
> isnull(estates.B_HOUSEROOM,0) as est_HouseRoom,
> isnull(estates.B_OWNENTRANCE,0) as est_OwnEntrance,
> isnull(estates.B_OWNTOILET,0) as est_OwnToilet,
> isnull(estates.B_BATHROOM,0) as est_Bathroom,
> isnull(estates.B_BATHROOMPART,0) as est_BathroomPart,
> isnull(estates.B_LIVINGROOM,0) as est_LivingRoom,
> isnull(estates.B_INCHEAT,0) as est_IncHeat,
> isnull(estates.B_CABLETV,0) as est_CableTV,
> isnull(estates.B_INCWATER,0) as est_IncWater,
> isnull(estates.B_BROADBAND,0) as est_Broadband,
> isnull(estates.B_GARBAGEREM,0) as est_GarbageRem,
> isnull(estates.B_PHONE,0) as est_Phone,
> isnull(estates.B_INCELEC,0) as est_IncElec,
> isnull(estates.B_GAS,0) as est_Gas,
> isnull(estates.B_CLOSETRAIN,0) as est_CloseTrain,
> isnull(estates.B_CLOSETUBE,0) as est_CloseTube,
> isnull(estates.B_CLOSEBUS,0) as est_CloseBus,
> isnull(estates.B_CLOSETVAR,0) as est_CloseTvar,
> isnull(estates.B_CLOSEDOWNTOWN,0) as est_CloseDowntown,
> isnull(estates.B_QUIETAREA,0) as est_QuietArea,
> isnull(estates.B_CLOSENATURE,0) as est_CloseNature,
> isnull(estates.B_SEAVIEW,0) as est_SeaView,
> isnull(estates.B_NOPETS,0) as est_NoPets,
> isnull(estates.B_NOSMOKERS,0) as est_NoSmokers,
> isnull(estates.B_NOKIDS,0) as est_NoKids,
> isnull(estates.B_ONLYWEEKDAYS,0) as est_OnlyWDays,
> isnull(estates.B_MAN,0) as est_Man,
> isnull(estates.B_WOMAN,0) as est_Woman,
> Estates.S_RentAdvance as est_RentAdvance,
> Estates.L_Rent as est_Rent,
> isnull(Estates.B_Permit,0) as est_Permit,
> Estates.S_Landlord as est_Landlord,
> Estates.S_Landl_Phone as est_LandlordPhone,
> Convert(nvarchar(6), Estates.Dat_From, 12) as est_DatFrom,
> Convert(nvarchar(6), Estates.Dat_Until, 12) as est_DatTo,
> isnull(Estates.B_MaybeLonger,0) as est_MaybeLonger,
> Estates.S_Description as est_Description,
> isnull(estates.N_STATE,0) as est_State,
> isnull(Estates.B_Printed,0) as est_Printed,
> isnull(estates.B_EXCLUSIVE,0) as est_Exclusive,
> Estates.L_RegisteredID as est_RegisteredID,
> Estates.Dat_Registered as est_DatRegistered,
> Estates.L_UpdatedID as est_UpdatedID,
> Estates.Dat_Updated as est_DatUpdated,
> Estates.Dat_Commit_Printed as est_DatCommitPrinted,
> Estates.S_TypeBekr as est_TypeCommit,
> Estates.S_Comments as est_Comments,
> Estates.L_AuthorizedID as est_AuthorizedID,
> Convert(nvarchar(10), Estates.Dat_Show, 120) as est_DatShow,
> estates.L_AgentID as est_AgentID
>FROM
> estates
> left join multimedia on multimedia.l_estateid=estates.l_estateid and
>isnull(N_INDEX,1)=1
> left join member with (nolock) on estates.l_memberid=member.l_memberid
>WHERE
> isnull(estates.B_FIRSTHAND,0) = 0 AND
> isnull(dat_show,getdate()) <= getdate() AND
> (ISNULL(MULTIMEDIA.N_INDEX,0) = 0 AND
> ISNULL(MULTIMEDIA.N_TYPE,0) = 0) AND
> ((@.RoomsMin = -1) OR (estates.D_ROOMS >= @.RoomsMin)) AND
> ((@.RoomsMax = -1) OR (estates.D_ROOMS <= @.RoomsMax)) AND
> ((@.RentMin = -1) OR (estates.L_RENT >= @.RentMin)) AND
> ((@.RentMax = -1) OR (estates.L_RENT <= @.RentMax)) AND
> ((@.SizeMin = -1) OR (estates.D_AREA >= @.SizeMin)) AND
> ((@.SizeMax = -1) OR (estates.D_AREA <= @.SizeMax)) AND
> ((@.PeriodMin = -1) OR ((case when estates.dat_until is null then
>10000 else datediff(dd, isnull(case when dat_from<getdate() then
>getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
>end)>= @.PeriodMin)) AND
> ((@.PeriodMax = -1) OR ((case when estates.dat_until is null then
>10000 else datediff(dd, isnull(case when dat_from<getdate() then
>getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
>end)<= @.PeriodMax)) AND
> ((@.EstateType = -1) OR (estates.N_TYPE = @.EstateType)) AND
> (((@.ZipArea=1) AND( estates.l_zipcode BETWEEN 10000 AND 19999 OR
> estates.l_zipcode BETWEEN 76100 AND 76295))OR
> ((@.ZipArea=2) AND( estates.l_zipcode BETWEEN 40001 AND 54999 OR
> estates.l_zipcode BETWEEN 66010 AND 66899))OR
> ((@.ZipArea=3) AND( estates.l_zipcode BETWEEN 20001 AND 29799))OR
> ((@.ZipArea=4) AND( estates.l_zipcode BETWEEN 30000 AND 39999 OR
> estates.l_zipcode BETWEEN 55000 AND 66090 OR
> estates.l_zipcode BETWEEN 66900 AND 76099 OR
> estates.l_zipcode BETWEEN 76296 AND 99999))OR
> ((@.ZipArea=5) AND( estates.l_zipcode BETWEEN 11100 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=6) AND( estates.l_zipcode BETWEEN 12000 AND 12999 OR
> estates.l_zipcode BETWEEN 11743 AND 11743 OR
> estates.l_zipcode BETWEEN 11760 AND 11799))OR
> ((@.ZipArea=7) AND( estates.l_zipcode BETWEEN 14100 AND 14799 OR
> estates.l_zipcode BETWEEN 13500 AND 13699 OR
> estates.l_zipcode BETWEEN 13000 AND 13099))OR
> ((@.ZipArea=8) AND( estates.l_zipcode BETWEEN 13100 AND 13499))OR
> ((@.ZipArea=9) AND( estates.l_zipcode BETWEEN 16200 AND 16899))OR
> ((@.ZipArea=10)AND( estates.l_zipcode BETWEEN 16900 AND 17499))OR
> ((@.ZipArea=11)AND( estates.l_zipcode BETWEEN 18200 AND 19799))OR
> ((@.ZipArea=12)AND( estates.l_zipcode BETWEEN 11700 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=13)AND( estates.l_zipcode BETWEEN 15100 AND 15299))OR
> ((@.ZipArea=14)AND( estates.l_zipcode BETWEEN 16700 AND 16899))OR
> ((@.ZipArea=15)AND( estates.l_zipcode BETWEEN 16900 AND 17199))OR
> ((@.ZipArea=16)AND( estates.l_zipcode BETWEEN 17200 AND 17499))OR
> ((@.ZipArea=17)AND( estates.l_zipcode BETWEEN 17500 AND 17799))OR
> ((@.ZipArea=18)AND( estates.l_zipcode BETWEEN 19100 AND 19299))OR
> ((@.ZipArea=19)AND( estates.l_zipcode BETWEEN 76100 AND 76299))) AND
> ((@.State = -1)OR (estates.N_STATE = @.State)) AND
> ((@.Furnished = '-1')OR (estates.S_FURNITURES != @.Furnished)) AND
> ((@.PublDaysMax = -1)OR (DATEDIFF(dd, estates.DAT_SHOW,
>getdate())<=@.PublDaysMax)) AND
> ((@.PrivateCompany = -1)OR (estates.B_ONLYCOMPANIES =
>@.PrivateCompany)) AND
> ((@.Exclusive = -1)OR (estates.B_EXCLUSIVE = @.Exclusive)) AND
> ((@.Street = '')OR (UPPER(estates.S_ADDRESS) LIKE
>(UPPER(@.Street)+'%')))
>ORDER BY
> CASE WHEN @.Sort = 'est_Area' THEN estates.S_AREA ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatShow' THEN estates.dat_Show ELSE NULL END
>DESC,
> CASE WHEN @.Sort = 'est_State' THEN estates.n_State ELSE NULL END,
> CASE WHEN @.Sort = 'est_Type' THEN estates.n_Type ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rooms' THEN estates.d_Rooms ELSE NULL END,
> CASE WHEN @.Sort = 'est_Size' THEN estates.d_Area ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rent' THEN estates.l_Rent ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatFrom' THEN estates.dat_From ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatTo' THEN estates.dat_until ELSE NULL END
>
>|||As an example, all the zip code logic could be replaced with something like:
inner join ZipCodeAreas on ESTATES.L_ZIPCODE = ZipCodeAreas.zipcode
and ZipCodeAreas.ZipArea = @.ZipArea
If you use default parameters of -1 for min and 10000 (or 999999999) for
max
AND ((@.RoomsMin = -1)
OR (ESTATES.D_ROOMS >= @.RoomsMin))
AND ((@.RoomsMax = -1)
OR (ESTATES.D_ROOMS <= @.RoomsMax))
AND ((@.RentMin = -1)
OR (ESTATES.L_RENT >= @.RentMin))
AND ((@.RentMax = -1)
OR (ESTATES.L_RENT <= @.RentMax))
AND ((@.SizeMin = -1)
OR (ESTATES.D_AREA >= @.SizeMin))
AND ((@.SizeMax = -1)
OR (ESTATES.D_AREA <= @.SizeMax))
AND ((@.EstateType = -1)
OR (ESTATES.N_TYPE = @.EstateType))
can be rewritten to:
AND ESTATES.D_ROOMS >= @.RoomsMin
AND ESTATES.D_ROOMS <= @.RoomsMax
AND ESTATES.L_RENT >= @.RentMin
AND ESTATES.L_RENT <= @.RentMax
AND ESTATES.D_AREA >= @.SizeMin
AND ESTATES.D_AREA <= @.SizeMax
and if you you use date ranges
AND ((@.PeriodMin = -1)
OR ((CASE
WHEN ESTATES.DAT_UNTIL IS NULL THEN 10000
ELSE DATEDIFF(DD,ISNULL(CASE
WHEN DAT_FROM < GETDATE()
THEN GETDATE()
ELSE DAT_FROM
END,GETDATE()),ISNULL(DAT_UNTIL,'2999010
1'))
END) >= @.PeriodMin))
AND ((@.PeriodMax = -1)
OR ((CASE
WHEN ESTATES.DAT_UNTIL IS NULL THEN 10000
ELSE DATEDIFF(DD,ISNULL(CASE
WHEN DAT_FROM < GETDATE()
THEN GETDATE()
ELSE DAT_FROM
END,GETDATE()),ISNULL(DAT_UNTIL,'2999010
1'))
END) <= @.PeriodMax))
might be rewritten to:
DAT_FROM >= @.DateMin
DAT_UNTIL <= @.DateMax
Honestly, I'm not sure about the last one as I don't know exactly what date
logic you need regarding nulls and dates <= today or >= today, but you
should be able to simplify it. IF you dont allow nulls in these fields and
you use '29990101' for dat_until instead of a null it may make your life
easier.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1144164113.587997.300040@.z34g2000cwc.googlegroups.com...
> tonicvodka wrote:
> Here are some suggestions.
> 1. Get rid of all the ORs. For example if you set a default value for
> @.roomsmax of 10000 instead of -1 you should be able to just D_ROOMS
> BETWEEN @.roomsmin AND @.roomsmax. Same for the other variables.
> 2. Create a table of zipcodes to join with instead of using CASE
> expressions.
> 3. Pass in date From and To ranges instead of doing the date arithmetic
> in the query. You are presently forcing those DATEDIFFs to be performed
> for every row.
> 4. Make some columns non-nullable to eliminate the ISNULLs.
> The basic idea is to get expressions in your WHERE clause that are
> sargable - that means columns being compared to constants or to other
> columns without complex expressions on the columns themselves. Once
> you've achieved that you can take best advantage of any indexes you
> create.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks a million for all the suggestions!!!
I'm a bit unsure regarding the table ZipArea; would the best design
just be making it two columns? One for ZipArea and one for ZipCode? It
would get extremly tall then, seeing ZipArea 1's span is ZipCodes 10000
to19999 and 76100 to 76295.|||Thanks a million for all the suggestions!!!
They'll definitely come to use, I won't be able to set default
values to the null-columns right now though since it'll screw up the
running pages, but that's high priority.
I'm a bit unsure regarding the table ZipArea; would the best design
just be making it two columns? One for ZipArea and one for ZipCode? It
would get extremly tall then, being ZipArea 1's span is ZipCodes 10000
to 19999 and 76100 to 76295.
Thanks again for the help!|||tonicvodka (tonicvodka@.hotmail.com) writes:
> They'll definitely come to use, I won't be able to set default
> values to the null-columns right now though since it'll screw up the
> running pages, but that's high priority.
> I'm a bit unsure regarding the table ZipArea; would the best design
> just be making it two columns? One for ZipArea and one for ZipCode? It
> would get extremly tall then, being ZipArea 1's span is ZipCodes 10000
> to 19999 and 76100 to 76295.
Yes, I would make it a two-column table, so with ZipCode as key and
ZipArea as output. Of course, that beast would be a nightmare to maintain,
so I might consider another table for input that has ranges, and which
feeds the first table through the trigger. But I would try to avoid
that range table in the query, as it's probably not good for performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Then 2-column it is, thanks!|||Apart of removing the allow-null-columns I've implemented all of the
above suggestions except the date range and the difference is big.
Earlier response times on about 1.2 sec now lie on 0.3 sec when same
data is posted, excellent.
The date range I think is difficult to do anything about, since it's a
range the customer is requesting, e.g. I'm looking to rent an apartment
for the duration of at least 3 monthes and the most 6 monthes, while
the table holds data from and to which date the apartment is available.
Any sudden brainwave is welcome! Thanks all.|||If a customer is looking for a duration from 3 to 6 months, what is the
start of that duration?
Can you associate the duration with a specific date relative to today?
i.e. Customer one is looking for an apartment for 3 to 6 months, starting
today. If today happens to be 2006-01-01 then the customer wants the
apartment no later than today, at least until 2006-03-31 and no time after
2006-05-31. Now, your business rules will be different, but the same
general approach should work, no?
"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1144325221.351430.293750@.g10g2000cwb.googlegroups.com...
> Apart of removing the allow-null-columns I've implemented all of the
> above suggestions except the date range and the difference is big.
> Earlier response times on about 1.2 sec now lie on 0.3 sec when same
> data is posted, excellent.
> The date range I think is difficult to do anything about, since it's a
> range the customer is requesting, e.g. I'm looking to rent an apartment
> for the duration of at least 3 monthes and the most 6 monthes, while
> the table holds data from and to which date the apartment is available.
> Any sudden brainwave is welcome! Thanks all.
>

Cann't create Legacy (SQL 2000) Maintenance Plans

I recently installed the SQL Server 2005 Management Studio on my workstation
and the install of this new tool removed the old SQL Server 2000 Enterprise
Manager.
Since 99% of my installed server base is still SQL Server 2000, here’s my
question.
How am I supposed to manage my SQL Server 2000 instances with the SQL Server
2005 tools?
When I connect to a SQL Server 2000 instance, using SQL 2005 Management
Studio, I can see under Legacy -> Database Maintenance Plans, the existing
plans; however I cannot create any new Legacy Maintenance plans.
Kurt
It sounds bad but you should have thought of that before you upgraded then.
The documentation in 2005 clearly spells this out. If you need to change or
create new 2000 maintenance plans then you should have a machine with 2000
EM on it for that purpose. You must have chosen to upgrade your existing
instance instead of doing a side by side installation. There is no problem
running 2000 and 2005 on the same box and having both EM and SSMS running at
the same time. You can reinstall 2000 client tools but I am not sure if you
need to uninstall 2005 first or not. I would try it first and see.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"kmoskal@.newsgroups.nospam" <zork@.newsgroups.nospam> wrote in message
news:1FC128B9-88F0-42CA-8684-1445A06BA3B0@.microsoft.com...
>I recently installed the SQL Server 2005 Management Studio on my
>workstation
> and the install of this new tool removed the old SQL Server 2000
> Enterprise
> Manager.
> Since 99% of my installed server base is still SQL Server 2000, here’s my
> question.
> How am I supposed to manage my SQL Server 2000 instances with the SQL
> Server
> 2005 tools?
> When I connect to a SQL Server 2000 instance, using SQL 2005 Management
> Studio, I can see under Legacy -> Database Maintenance Plans, the existing
> plans; however I cannot create any new Legacy Maintenance plans.
>
>
>
> --
> Kurt

Cannot shrink log file

I know this is an age old question. I've looked through the posts and don't
find an answer.
I did a log backup this morning with the truncate option. Now I run "dbcc
shrinkfile" and get this:
Cannot shrink log file 2 (BSMOrders_Log) because all logical log files are
in use.
What causes this? I thought the backup would delete this data so the file
could be shrunk. It is still sitting at 12GB....Show all logical log files using DBCC Loginfo(<database id>). If status is 2
for last log file then you will not be able to shrink it. Status 2 means it's
an active log and currently being used. In that case you can run some dummy
transactions to bring it to 0. Also you can look for any open transactions
that are not committed yet by using DBCC OPENTRAN
"Tim Greenwood" wrote:
> I know this is an age old question. I've looked through the posts and don't
> find an answer.
> I did a log backup this morning with the truncate option. Now I run "dbcc
> shrinkfile" and get this:
> Cannot shrink log file 2 (BSMOrders_Log) because all logical log files are
> in use.
> What causes this? I thought the backup would delete this data so the file
> could be shrunk. It is still sitting at 12GB....
>
>|||Hey Bob
When you said "you can run some dummy transactions to bring it to 0",
could you please describe what does that really mean and how can
somebody run a transaction (can you please provide some sample SQL).
Sorry for lack of my knowledge on that
Thanks
PP
Bob wrote:
> Show all logical log files using DBCC Loginfo(<database id>). If status is 2
> for last log file then you will not be able to shrink it. Status 2 means it's
> an active log and currently being used. In that case you can run some dummy
> transactions to bring it to 0. Also you can look for any open transactions
> that are not committed yet by using DBCC OPENTRAN
> "Tim Greenwood" wrote:
> > I know this is an age old question. I've looked through the posts and don't
> > find an answer.
> > I did a log backup this morning with the truncate option. Now I run "dbcc
> > shrinkfile" and get this:
> >
> > Cannot shrink log file 2 (BSMOrders_Log) because all logical log files are
> > in use.
> >
> > What causes this? I thought the backup would delete this data so the file
> > could be shrunk. It is still sitting at 12GB....
> >
> >
> >|||Lookup 'shrinking the transaction log' in Books Online for a detailed
explanation.
--
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstorageengine/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
<prabhupr@.hotmail.com> wrote in message
news:1152712375.701470.10780@.i42g2000cwa.googlegroups.com...
> Hey Bob
> When you said "you can run some dummy transactions to bring it to 0",
> could you please describe what does that really mean and how can
> somebody run a transaction (can you please provide some sample SQL).
> Sorry for lack of my knowledge on that
> Thanks
> PP
> Bob wrote:
>> Show all logical log files using DBCC Loginfo(<database id>). If status
>> is 2
>> for last log file then you will not be able to shrink it. Status 2 means
>> it's
>> an active log and currently being used. In that case you can run some
>> dummy
>> transactions to bring it to 0. Also you can look for any open
>> transactions
>> that are not committed yet by using DBCC OPENTRAN
>> "Tim Greenwood" wrote:
>> > I know this is an age old question. I've looked through the posts and
>> > don't
>> > find an answer.
>> > I did a log backup this morning with the truncate option. Now I run
>> > "dbcc
>> > shrinkfile" and get this:
>> >
>> > Cannot shrink log file 2 (BSMOrders_Log) because all logical log files
>> > are
>> > in use.
>> >
>> > What causes this? I thought the backup would delete this data so the
>> > file
>> > could be shrunk. It is still sitting at 12GB....
>> >
>> >
>> >
>

Cannot shrink log file

I know this is an age old question. I've looked through the posts and don't
find an answer.
I did a log backup this morning with the truncate option. Now I run "dbcc
shrinkfile" and get this:
Cannot shrink log file 2 (BSMOrders_Log) because all logical log files are
in use.
What causes this? I thought the backup would delete this data so the file
could be shrunk. It is still sitting at 12GB....Show all logical log files using DBCC Loginfo(<database id> ). If status is 2
for last log file then you will not be able to shrink it. Status 2 means it'
s
an active log and currently being used. In that case you can run some dummy
transactions to bring it to 0. Also you can look for any open transactions
that are not committed yet by using DBCC OPENTRAN
"Tim Greenwood" wrote:

> I know this is an age old question. I've looked through the posts and don
't
> find an answer.
> I did a log backup this morning with the truncate option. Now I run "dbcc
> shrinkfile" and get this:
> Cannot shrink log file 2 (BSMOrders_Log) because all logical log files are
> in use.
> What causes this? I thought the backup would delete this data so the file
> could be shrunk. It is still sitting at 12GB....
>
>|||Hey Bob
When you said "you can run some dummy transactions to bring it to 0",
could you please describe what does that really mean and how can
somebody run a transaction (can you please provide some sample SQL).
Sorry for lack of my knowledge on that
Thanks
PP
Bob wrote:[vbcol=seagreen]
> Show all logical log files using DBCC Loginfo(<database id> ). If status is
2
> for last log file then you will not be able to shrink it. Status 2 means i
t's
> an active log and currently being used. In that case you can run some dumm
y
> transactions to bring it to 0. Also you can look for any open transactions
> that are not committed yet by using DBCC OPENTRAN
> "Tim Greenwood" wrote:
>|||Lookup 'shrinking the transaction log' in Books Online for a detailed
explanation.
Paul Randal
Lead Program Manager, Microsoft SQL Server Storage Engine
http://blogs.msdn.com/sqlserverstor...ne/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
<prabhupr@.hotmail.com> wrote in message
news:1152712375.701470.10780@.i42g2000cwa.googlegroups.com...
> Hey Bob
> When you said "you can run some dummy transactions to bring it to 0",
> could you please describe what does that really mean and how can
> somebody run a transaction (can you please provide some sample SQL).
> Sorry for lack of my knowledge on that
> Thanks
> PP
> Bob wrote:
>

Cannot see network drive when doing restore from disk

Hello, I recently had the PC replaced where I ran SQL Server 2005.

On the old PC I was able to backup and restore DB on a network drive L: w/o any trouble. On the new PC I reinstalled SQL Server making sure that all the settings are exactly as before. However, when I tried to restore the database, I go to "From device" -> Add file, but I do not see L: drive on the list anymore.

However, I seem to have an access to the network in general, as I can open and run sql queries that are stored on the same network drive.

I am spending the whole day comparing 2 machine setting by setting, and cannot find anything different except that the old system was running Win2000, and new one is WinXP, but I doubt that it has anything to do with it.

I also did not see any similar problem reported elsewhere.

Does anyone have any idea what to look for?

Make sure you have the drive mapped correctly on your system.

you may need to copy and paste the UNC location.

I'm not sure how it works on a local machine, but you may want to make sure the login running the SQL service has access to that network drive as well.

It would probably work better for you to just copy a backup file down from that drive to a local drive and restore from there.

Cannot resolve the collation conflict between

Hi
I have a new DB, in this new DB I have import and copy some data from the
old DB, among others, a report
When i try to open the report, I get this message
- Cannot resolve the collation conflict between
"SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
Any ideas.
ThanksHi
Your report's query contains JOIN on varchar/nvarchar columns which have
different collation
Try
select * from table join anothertable on table.col =anothertable.column
COLLATE danish_norwegian_ci_as
"H.Gjerde" <hg@.norspace.no> wrote in message
news:%23zJ%23Lv5PHHA.2468@.TK2MSFTNGP06.phx.gbl...
> Hi
> I have a new DB, in this new DB I have import and copy some data from the
> old DB, among others, a report
> When i try to open the report, I get this message
> - Cannot resolve the collation conflict between
> "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
>
> Any ideas.
> Thanks
>|||Thank you
It help, in my plain sql script
But I stil have the problem, because I not realy sure where to put it in my
CrystalReport. The report is predefined.
...? :|
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uHN$g25PHHA.3268@.TK2MSFTNGP03.phx.gbl...
> Hi
> Your report's query contains JOIN on varchar/nvarchar columns which have
> different collation
> Try
> select * from table join anothertable on table.col =anothertable.column
> COLLATE danish_norwegian_ci_as
>
>
> "H.Gjerde" <hg@.norspace.no> wrote in message
> news:%23zJ%23Lv5PHHA.2468@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> I have a new DB, in this new DB I have import and copy some data from the
>> old DB, among others, a report
>> When i try to open the report, I get this message
>> - Cannot resolve the collation conflict between
>> "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
>>
>> Any ideas.
>> Thanks
>|||Hi
I have no idea. I don't know Crystal Report
"H.Gjerde" <hg@.norspace.no> wrote in message
news:eeyQ5F6PHHA.3344@.TK2MSFTNGP02.phx.gbl...
> Thank you
> It help, in my plain sql script
> But I stil have the problem, because I not realy sure where to put it in
> my CrystalReport. The report is predefined.
> ...? :|
>
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uHN$g25PHHA.3268@.TK2MSFTNGP03.phx.gbl...
>> Hi
>> Your report's query contains JOIN on varchar/nvarchar columns which have
>> different collation
>> Try
>> select * from table join anothertable on table.col =anothertable.column
>> COLLATE danish_norwegian_ci_as
>>
>>
>> "H.Gjerde" <hg@.norspace.no> wrote in message
>> news:%23zJ%23Lv5PHHA.2468@.TK2MSFTNGP06.phx.gbl...
>> Hi
>> I have a new DB, in this new DB I have import and copy some data from
>> the old DB, among others, a report
>> When i try to open the report, I get this message
>> - Cannot resolve the collation conflict between
>> "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
>>
>> Any ideas.
>> Thanks
>>
>|||can you create SP and call it in Crystal Report
Regards
Amish shah
http://shahamishm.tripod.com
On Jan 24, 3:31 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> I have no idea. I don't know Crystal Report
> "H.Gjerde" <h...@.norspace.no> wrote in messagenews:eeyQ5F6PHHA.3344@.TK2MSFTNGP02.phx.gbl...
>
> > Thank you
> > It help, in my plain sql script
> > But I stil have the problem, because I not realy sure where to put it in
> > my CrystalReport. The report is predefined.
> > ...? :|
> > "Uri Dimant" <u...@.iscar.co.il> wrote in message
> >news:uHN$g25PHHA.3268@.TK2MSFTNGP03.phx.gbl...
> >> Hi
> >> Your report's query contains JOIN on varchar/nvarchar columns which have
> >> different collation
> >> Try
> >> select * from table join anothertable on table.col =anothertable.column
> >> COLLATE danish_norwegian_ci_as
> >> "H.Gjerde" <h...@.norspace.no> wrote in message
> >>news:%23zJ%23Lv5PHHA.2468@.TK2MSFTNGP06.phx.gbl...
> >> Hi
> >> I have a new DB, in this new DB I have import and copy some data from
> >> the old DB, among others, a report
> >> When i try to open the report, I get this message
> >> - Cannot resolve the collation conflict between
> >> "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
> >> Any ideas.
> >> Thanks- Hide quoted text -- Show quoted text -|||H.Gjerde wrote:
> Hi
> I have a new DB, in this new DB I have import and copy some data from the
> old DB, among others, a report
> When i try to open the report, I get this message
> - Cannot resolve the collation conflict between
> "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
>
> Any ideas.
> Thanks
>
Hi
The new database you have created, seems to be created with a different
collation than the old one. If you can't change the SQL code behind the
report, your best option might be to drop the new database, create a new
one with the correct collation and then import the data again.
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||you might find that it is in fact the tempdb has a different collation
than the user database. You might have to rebuild the system databases
using "rebuildm".
On Jan 24, 11:30 am, "Steen Schl=FCter Persson (DK)"
<steen@.REMOVE_THIS_asavaenget.dk> wrote:
> H.Gjerde wrote:
> > Hi
> > I have a new DB, in this new DB I have import and copy some data from t=he
> > old DB, among others, a report
> > When i try to open the report, I get this message
> > - Cannot resolve the collation conflict between
> > "SQL_Latin1_General_CP1_CI_AS" and "danish_norwegian_ci_as" in the equal
> > Any ideas.
> > ThanksHi
> The new database you have created, seems to be created with a different
> collation than the old one. If you can't change the SQL code behind the
> report, your best option might be to drop the new database, create a new
> one with the correct collation and then import the data again.
> --
> Regards
> Steen Schl=FCter Persson
> Database Administrator / System Administrator