December 20, 2016 at 8:19 am
Hi,
I need a search routine for a carrier company to search for following parameters (you can mix them up the way you like - choose only one or all..):
customer
carrier
employee
date_from
date_to
plate_number_truck
plate_number_trailer
country_loaded
zipCode_loaded (with like operator)
city_unloaded (with like operator)
country_unloaded
zipCode_unloaded (with like operator)
city_unloaded (with like operator)
so that you can search a transport from Poland zip code started with 3 to Italy (any combination possible) e.g.
There are tables for Customer, Carrier, Employee, Truck, Trailer but to simplify the question I skip them.
Approx. 300.000 records in file table
create database Truckroute
go
USE Truckroute
GO
CREATE TABLE dbo.tblCity(
ID_City int IDENTITY(1,1) NOT NULL primary key,
FK_Country varchar(10), -- references,
Zipcode varchar(10) ,
City varchar(100)
)
CREATE TABLE dbo.tblFile(
ID_File int IDENTITY(1,1) NOT NULL primary key,
Filedate smalldatetime
)
CREATE TABLE dbo.tblFile_Carrier(
ID_File_Carrier int IDENTITY(1,1) NOT NULL primary key,
FK_File int references tblFile,
FK_Carrier int, --references tblCarrier,
Truck_Plate varchar(100) NULL,
Trailer_Plate varchar(100) NULL
)
CREATE TABLE dbo.tblFile_Customer(
ID_File_Customer int IDENTITY(1,1) NOT NULL primary key,
FK_File int references tblFile,
FK_Customer int --references tblCustomer,
)
CREATE TABLE dbo.tblRoute(
ID_Route int IDENTITY(1,1) NOT NULL primary key,
FK_File_Customer int references tblFile_Customer,
FK_City int, -- references,
Routedate smalldatetime ,
unload_freight bit
)
go
-- inserts
insert into tblFile values(getdate()),('3.10.2016')
-- select * from tblFile
insert into tblFile_Customer values(1,1),(1,2),(2,2)
--select * from tblFile_Customer
insert into tblCity values
('AUT','5020','Salzburg'),
('AUT','1030','Wien'),
('AUT','4020','Linz'),
('ITA','2222','Verona'),
('ITA','1010','Rom'),
('HUN','1254','Budapest')
insert into tblFile_Carrier values
(1,3,'KO-123HU','KO-Aufl'),
(2,1,'FREMD_LKW_KENN','FREMD_AUFL_KENN')
insert into tblRoute values
(1,1,'8.11.2016',0),(1,2,'8.11.2016',0),(1,4,'9.11.2016',1),(1,5,'10.11.2016',1),
(2,4,'11.11.2016',0),(2,2,'12.11.2016',1),
(3,1,'4.10.2016',0),(3,3,'5.10.2016',0),(3,6,'5.10.2016',1)
go
One Tour is one file and can have more customers and more carriers and also more places to load and unload stuff.
I created a view with index (index didn't work - but this is a different problem)
then a table valued function on the view with where-clause to filter customer, employee, date, carrier.
Then I made a scalar function which returns true if there is a record for country, zipcode and/or city to load or unload
and I used this scalar function in the where-clause from the table valued function.
This worked fine with my testdata but in real database it dosn't work at all - more than 3 minutes.
The problem seams to be that in the route table a record can either be for load or for unload (with bit flag) and in the search you are able to search for files which
have a load AND/or an unload Country/city/zipcode.
Here is the code:
if object_id('fLoad') > 0 drop function fLoad
go
create function fLoad(
@IDFile int,
@country varchar(10),
@Zip varchar(10) ,
@City varchar(200),
@unload bit)
returns bit
as
begin
declare @result bit=0
if exists (select ID_Route
from tblRoute
join tblCity on FK_City = ID_City
where FK_File_Customer in(select ID_File_Customer from tblFile_Customer where FK_File = @IDFile)
and unload_freight = @unload
and (FK_country = @Country or @Country is null)
and (City like @City+'%' or @City is null)
and (Zipcode like @Zip+'%' or @Zip is null))
set @result = 1
return @result
end
go
/*
select dbo.fLoad(2,'AUT',NULL,NULL,0)
*/
if object_id('vFile_Search') > 0 drop view vFile_Search
go
create view vFile_Search
with schemabinding
as
select ID_File, Filedate,
FK_Customer, unload_freight,
ID_File_Customer, Routedate,
FK_City, ID_Route
from dbo.tblFile as a
join dbo.tblFile_Customer on ID_File = FK_File
join dbo.tblRoute l on ID_File_Customer = FK_File_Customer
go
-- works rather slow as Index didn't work
-- select * from dbo.vFile_Search
if object_id('fFile_Search') > 0 drop function fFile_Search
go
create function fFile_Search(
@date_from date ,
@date_to date,
@ID_Customer int)
returns table
as
return(select *
from vFile_Search
where (FK_Customer = @ID_Customer or @ID_Customer is null)
and (Routedate >= @date_from or @date_from is null)
and (Routedate < dateadd(day,1,@date_to) or @date_to is null)
)
go
/*
select * from fFile_Search('4.10.2016','12.11.2016', 1)
SJ: works ok
*/
if object_id('pFile_Search') > 0 drop proc pFile_Search
go
create proc pFile_Search
@date_from date = NULL,
@date_to date = NULL,
@ID_Customer int = NULL,
@CountryL varchar(50) = NULL,
@CountryE varchar(50) = NULL,
@ZipL varchar(50) = NULL,
@ZipE varchar(50) = NULL,
@CityL varchar(255) = NULL,
@CityE varchar(255) = NULL,
@carrier int=NULL,
@Truck varchar(255)= NULL,
@Trailer varchar(255)= NULL
as
begin
select ID_File, Routedate, FK_Customer,
FK_Carrier, isnull([Truck_Plate],'') as TruckPlate, isnull([Trailer_Plate],'') as TrailerPlate ,
unload_freight, ID_File_Customer, FK_City
into #t1
from fFile_Search(@date_from,@date_to,@ID_Customer)
-- join tblCity on ID_City = FK_City
left join tblFile_Carrier on ID_File = FK_File
where(FK_Carrier = @carrier or @carrier is null)
and (@Truck is null or [Truck_Plate] like @Truck+'%')
and (@Trailer is null or [Trailer_Plate] like @Trailer+'%' )
-- works okay but not really fine
select * from #t1
where dbo.fLoad(ID_File,@CountryL,@ZipL,@CityL,0) = 1
and dbo.fLoad(ID_File,@CountryE,@ZipE,@CityE,1) = 1
-- works poorly althoug only 70 records are in #t1 when testing in real database more than 2 minutes
end
go
exec pFile_Search
@date_from = '1.8.2016',
@date_to = NULL,
@ID_Customer = 1,
@CountryL = 'AUT',
@CountryE = NULL,
@ZipL = NULL,
@ZipE = NULL,
@CityL = NULL,
@CityE = NULL,
@carrier =NULL,
@Truck = NULL,
@Trailer = NULL
Any samples how I can make this work? :w00t:
Thank you,
Sue
Susanne
December 20, 2016 at 8:28 am
Nice job posting ddl and sample data in addition to the code you have. 😀 You have several performance issues going on here. First is the scalar function, they are notoriously horrible for performance. Then you have your queries, this type of query is commonly referred to as a catch-all query and are very common. In fact, the style you wrote your query is very common. The other common issue with this type of query is that the performance is less than ideal. All is not lost however, Gail has a great article that goes into detail about how to deal with this type of query and make is super fast. Check out her blog post here. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 20, 2016 at 8:37 am
Erland Sommarskog maintains an article describing various techniques and considerations for implementing this type of generic search procedure where the entities, predicates, and parameters are broadly defined.
Dynamic Search Conditions in T-SQL
http://sommarskog.se/dyn-search.html
From my experience, the best approach, in terms of flexibility and performance, hinges around building a dynamic SQL select based on what combination of input parameters are supplied.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 20, 2016 at 8:39 am
Eric M Russell (12/20/2016)
Erland Sommarskog maintains an article describing various techniques and considerations for implementing this type of generic search procedure where the entities, predicates, and parameters are broadly defined.Dynamic Search Conditions in T-SQL
http://sommarskog.se/dyn-search.html
From my experience, the best approach, in terms of flexibility and performance, hinges around building a dynamic SQL select based on what combination of input parameters are supplied.
Agreed. Gail's blog post goes into the dynamic sql approach to this problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 21, 2016 at 12:44 pm
Hi,
thanks for the hint with dynamic SQL (I didn't know that it is faster and better than the query I wrote and always thought that it is nasty (SQL injection) but Gails article helped me to understand). 😀
I have 13 parameters which can be combined in any way ... maybe a mix of dynamic SQL and the " or @para is null" statement??
Very often the search criteria will be: from city/country/zipcode A (load something) to city/country/zipcode B (unload something) in the same transport file. The route from the truck is mention in tblRoute. Every city is one record with information if load or unload.
As my function is very slow shall I try a join to tblRoute two times instead?
something like this:
select *
from tblFile
join tblFile_customer on...
join tblRoute as RouteLoad on...
join tblRoute as RouteUnload on...
where RouteLoad.city = @CityL -- or dynamic clause here but 6 parameters (country/city/zipcode for load and unload) ??
I really highly appreciate further help :Wow:
Susanne
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply