HELP HELP varchar

I have a field type of "varchar"

It can not accept to Store Arabic Characters!!! plus i can't change it into ntext or nvarchar my system which goin to export the data into SQL server by suing insert command will hang. so im stucked with varchar!!!

here some sample of my data stored currently in My SQL SERVER 2000

" /   "

Plzzzz help me if there any other way to do it

Do you have the right collation set in your database?

Have a look in BOL (Books on Line) at COLLATE - there's a lot of info in there about how to deal with charachters from different languages and alphabets.

/Kenneth

|||If you want to store arabic characters you will have to change your column data types to nvarchar. So you will have to investigate the problem you are describing while using the nvarchar column. What is the actual problem here ? I am not sure / doubt that this is related to collation problems, but further investigation might help us to solve the problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.desql

Help for Fine Tune Store procedure to increase its performance

I want to change following store procedure, it takes around 5 minutes to
execute & I calls for each vessel / voyage combination... , Could any one
help me to fine tune this store procedure.
CREATE PROCEDURE get_ContainerListinExpBL
@.VesselCode varchar(10) ,
@.VoyageCode varchar(10),
@.LinerCode varchar(10) ,
@.BookingList varchar(1000) = null ,
@.BLNumber varchar(20) = null
AS
select distinct t.ContainerNo,
T.ContainerType,
(select BookingNote
from trnActivity TA
inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
TA.LinerCode
where TA.ContainerNo = T.ContainerNo
and TA.LinerCode = t.LinerCode
and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and DO is not Null
and TA.ActivityDate =
(select Max(activityDate ) from
trnActivity TB
where TB.ContainerNo = t.ContainerNo
and TB.LinerCode = t.LinerCode
and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and TB.DO is not null)) BookingNote ,
t.ImpVesselCode ,
t.ImpVoyageCode ,
t.ReleaseRefNo
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
union
select
c.ContainerNo,
c.ContainerType,
c.BookingNote,
c.ImpVesselCode ,
c.ImpVoyageCode ,
c.ReleaseRefNo
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
GO
-- --
Thanks in advance
Please also post DDL (CREATE TABLE statements, including constraints and
indexes). The underlying schema can affect query performance significantly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nitu" <Nitu@.discussions.microsoft.com> wrote in message
news:05229BAA-ABA5-4C85-A298-616D82F629D3@.microsoft.com...
>I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
>
> -- --
> Thanks in advance
|||Nitu,
You should post the DDL, including all indexes, that is key. Also, ask
yourself if you really need to handle NULL values in VesselCode and/or
VoyageCode, because the use of ISNULL() will disallow the use of an
index for those columns.
Gert-Jan
Nitu wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
|||On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
Hi, try removing the union and use a temp table instead. For example:
select distinct t.ContainerNo etc...
into #my_temp
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
insert #mytemp
select distinct
c.ContainerNo etc...
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
and not exists (select 1 from #my_temp where #my_temp. ContainerNo =
c. ContainerNo
and #my_temp. ContainerType = c. ContainerType and #my_temp.
BookingNote = c. BookingNote
and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
ReleaseRefNo )
That should move the timetable to less than 5 minutes to run. I think
that is acceptable. You can fine tune it further by using more #temp
tables for inner subqueries and joining them with #my_temp using
updates statements.
|||> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
SB, how can you make these recommendations without knowledge of the
underlying schema? I'm not saying that employing a temp tables won't
improve performance, it's just that I've often seen temp tables used as a
kludge workaround when the real issue is lack of useful indexes and
non-saragable query expressions. IMHO, a better approach is to understand
and address the underlying cause for the poor performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1185777942.351695.318730@.q75g2000hsh.googlegr oups.com...
> On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> Hi, try removing the union and use a temp table instead. For example:
> select distinct t.ContainerNo etc...
> into #my_temp
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> insert #mytemp
> select distinct
> c.ContainerNo etc...
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> and not exists (select 1 from #my_temp where #my_temp. ContainerNo =
> c. ContainerNo
> and #my_temp. ContainerType = c. ContainerType and #my_temp.
> BookingNote = c. BookingNote
> and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> ReleaseRefNo )
>
> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
>
|||On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> SB, how can you make these recommendations without knowledge of the
> underlying schema? I'm not saying that employing a temp tables won't
> improve performance, it's just that I've often seen temp tables used as a
> kludge workaround when the real issue is lack of useful indexes and
> non-saragable query expressions. IMHO, a better approach is to understand
> and address the underlying cause for the poor performance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1185777942.351695.318730@.q75g2000hsh.googlegr oups.com...
>
>
>
>
> - Show quoted text -
Dan I don't need schema. If I need one I will ask tl from you. Thanks.
|||With all due respect SB, if you want to provide real help to the OP you DO
need schema, indexes and possibly even data distribution information. Your
stab in the dark could well be WORSE performing due the large I/O overhead
associated with temporary tables (and the sproc recompiles associated with
them too). Even worse is that it completely avoids the possibility that a
single appropriate index addition could improve the sproc performance 1-4
orders of magnitude.
We on this forum should strive to give posters the right tools to help them
in the future as well as simply attempting to solve their current problem.
With just a bit more information from the OP we could have a dialog that
provided good mentoring to all while providing the BEST solution to Nitu.
TheSQLGuru
President
Indicium Resources, Inc.
"SB" <othellomy@.yahoo.com> wrote in message
news:1185852523.248498.24130@.d55g2000hsg.googlegro ups.com...
> On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
> Dan I don't need schema. If I need one I will ask tl from you. Thanks.
>

Help for Fine Tune Store procedure to increase its performance

I want to change following store procedure, it takes around 5 minutes to
execute & I calls for each vessel / voyage combination... , Could any one
help me to fine tune this store procedure.
--
CREATE PROCEDURE get_ContainerListinExpBL
@.VesselCode varchar(10) ,
@.VoyageCode varchar(10),
@.LinerCode varchar(10) ,
@.BookingList varchar(1000) = null ,
@.BLNumber varchar(20) = null
AS
select distinct t.ContainerNo,
T.ContainerType,
(select BookingNote
from trnActivity TA
inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner = TA.LinerCode
where TA.ContainerNo = T.ContainerNo
and TA.LinerCode = t.LinerCode
and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and DO is not Null
and TA.ActivityDate = (select Max(activityDate ) from
trnActivity TB
where TB.ContainerNo = t.ContainerNo
and TB.LinerCode = t.LinerCode
and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and TB.DO is not null)) BookingNote ,
t.ImpVesselCode ,
t.ImpVoyageCode ,
t.ReleaseRefNo
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
union
select
c.ContainerNo,
c.ContainerType,
c.BookingNote,
c.ImpVesselCode ,
c.ImpVoyageCode ,
c.ReleaseRefNo
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
GO
-- --
Thanks in advancePlease also post DDL (CREATE TABLE statements, including constraints and
indexes). The underlying schema can affect query performance significantly.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Nitu" <Nitu@.discussions.microsoft.com> wrote in message
news:05229BAA-ABA5-4C85-A298-616D82F629D3@.microsoft.com...
>I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
>
> -- --
> Thanks in advance|||Nitu,
You should post the DDL, including all indexes, that is key. Also, ask
yourself if you really need to handle NULL values in VesselCode and/or
VoyageCode, because the use of ISNULL() will disallow the use of an
index for those columns.
Gert-Jan
Nitu wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance|||On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner => TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate => (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
Hi, try removing the union and use a temp table instead. For example:
select distinct t.ContainerNo etc...
into #my_temp
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
insert #mytemp
select distinct
c.ContainerNo etc...
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
and not exists (select 1 from #my_temp where #my_temp. ContainerNo =c. ContainerNo
and #my_temp. ContainerType = c. ContainerType and #my_temp.
BookingNote = c. BookingNote
and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
ReleaseRefNo )
That should move the timetable to less than 5 minutes to run. I think
that is acceptable. You can fine tune it further by using more #temp
tables for inner subqueries and joining them with #my_temp using
updates statements.|||> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
SB, how can you make these recommendations without knowledge of the
underlying schema? I'm not saying that employing a temp tables won't
improve performance, it's just that I've often seen temp tables used as a
kludge workaround when the real issue is lack of useful indexes and
non-saragable query expressions. IMHO, a better approach is to understand
and address the underlying cause for the poor performance.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
> On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
>> I want to change following store procedure, it takes around 5 minutes to
>> execute & I calls for each vessel / voyage combination... , Could any
>> one
>> help me to fine tune this store procedure.
>> --
>> CREATE PROCEDURE get_ContainerListinExpBL
>> @.VesselCode varchar(10) ,
>> @.VoyageCode varchar(10),
>> @.LinerCode varchar(10) ,
>> @.BookingList varchar(1000) = null ,
>> @.BLNumber varchar(20) = null
>> AS
>> select distinct t.ContainerNo,
>> T.ContainerType,
>> (select BookingNote
>> from trnActivity TA
>> inner join trnReleasenote RA on
>> RA.ReleaseNoteCode = TA.DO and RA.Liner =>> TA.LinerCode
>> where TA.ContainerNo = T.ContainerNo
>> and TA.LinerCode = t.LinerCode
>> and isnull(TA.VesselCode,'') =>> isnull(t.ImpVesselcode ,'')
>> and isnull(TA.VoyageCode, '') =>> isnull(t.ImpVoyageCode, '')
>> and DO is not Null
>> and TA.ActivityDate =>> (select Max(activityDate ) from
>> trnActivity TB
>> where TB.ContainerNo = t.ContainerNo
>> and TB.LinerCode = t.LinerCode
>> and isnull(TB.VesselCode,'') =>> isnull(t.ImpVesselCode , '')
>> and isnull(TB.VoyageCode, '') =>> isnull(t.ImpVoyageCode, '')
>> and TB.DO is not null)) BookingNote ,
>> t.ImpVesselCode ,
>> t.ImpVoyageCode ,
>> t.ReleaseRefNo
>> from trnExpTracking t
>> --inner join mstISO O on O.ContainerType = T.ContainerType
>> where t.ExpVesselCode = @.VesselCode
>> and t.ExpVoyagecode = @.VoyageCode
>> and t.LinerCode = @.LinerCode
>> union
>> select
>> c.ContainerNo,
>> c.ContainerType,
>> c.BookingNote,
>> c.ImpVesselCode ,
>> c.ImpVoyageCode ,
>> c.ReleaseRefNo
>> from trnExpContainerDetail c
>> where C.LinerCode = @.LinerCode
>> and C.VesselCode = @.VesselCode
>> and C.VoyageCode = @.VoyageCode
>> and C.BLnumber = @.BLnumber
>> GO
>> -- --
>> Thanks in advance
> Hi, try removing the union and use a temp table instead. For example:
> select distinct t.ContainerNo etc...
> into #my_temp
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> insert #mytemp
> select distinct
> c.ContainerNo etc...
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> and not exists (select 1 from #my_temp where #my_temp. ContainerNo => c. ContainerNo
> and #my_temp. ContainerType = c. ContainerType and #my_temp.
> BookingNote = c. BookingNote
> and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> ReleaseRefNo )
>
> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
>|||On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > That should move the timetable to less than 5 minutes to run. I think
> > that is acceptable. You can fine tune it further by using more #temp
> > tables for inner subqueries and joining them with #my_temp using
> > updates statements.
> SB, how can you make these recommendations without knowledge of the
> underlying schema? I'm not saying that employing a temp tables won't
> improve performance, it's just that I've often seen temp tables used as a
> kludge workaround when the real issue is lack of useful indexes and
> non-saragable query expressions. IMHO, a better approach is to understand
> and address the underlying cause for the poor performance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>
> > On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> >> I want to change following store procedure, it takes around 5 minutes to
> >> execute & I calls for each vessel / voyage combination... , Could any
> >> one
> >> help me to fine tune this store procedure.
> >> --
> >> CREATE PROCEDURE get_ContainerListinExpBL
> >> @.VesselCode varchar(10) ,
> >> @.VoyageCode varchar(10),
> >> @.LinerCode varchar(10) ,
> >> @.BookingList varchar(1000) = null ,
> >> @.BLNumber varchar(20) = null
> >> AS
> >> select distinct t.ContainerNo,
> >> T.ContainerType,
> >> (select BookingNote
> >> from trnActivity TA
> >> inner join trnReleasenote RA on
> >> RA.ReleaseNoteCode = TA.DO and RA.Liner => >> TA.LinerCode
> >> where TA.ContainerNo = T.ContainerNo
> >> and TA.LinerCode = t.LinerCode
> >> and isnull(TA.VesselCode,'') => >> isnull(t.ImpVesselcode ,'')
> >> and isnull(TA.VoyageCode, '') => >> isnull(t.ImpVoyageCode, '')
> >> and DO is not Null
> >> and TA.ActivityDate => >> (select Max(activityDate ) from
> >> trnActivity TB
> >> where TB.ContainerNo = t.ContainerNo
> >> and TB.LinerCode = t.LinerCode
> >> and isnull(TB.VesselCode,'') => >> isnull(t.ImpVesselCode , '')
> >> and isnull(TB.VoyageCode, '') => >> isnull(t.ImpVoyageCode, '')
> >> and TB.DO is not null)) BookingNote ,
> >> t.ImpVesselCode ,
> >> t.ImpVoyageCode ,
> >> t.ReleaseRefNo
> >> from trnExpTracking t
> >> --inner join mstISO O on O.ContainerType = T.ContainerType
> >> where t.ExpVesselCode = @.VesselCode
> >> and t.ExpVoyagecode = @.VoyageCode
> >> and t.LinerCode = @.LinerCode
> >> union
> >> select
> >> c.ContainerNo,
> >> c.ContainerType,
> >> c.BookingNote,
> >> c.ImpVesselCode ,
> >> c.ImpVoyageCode ,
> >> c.ReleaseRefNo
> >> from trnExpContainerDetail c
> >> where C.LinerCode = @.LinerCode
> >> and C.VesselCode = @.VesselCode
> >> and C.VoyageCode = @.VoyageCode
> >> and C.BLnumber = @.BLnumber
> >> GO
> >> -- --
> >> Thanks in advance
> > Hi, try removing the union and use a temp table instead. For example:
> > select distinct t.ContainerNo etc...
> > into #my_temp
> > from trnExpTracking t
> > --inner join mstISO O on O.ContainerType = T.ContainerType
> > where t.ExpVesselCode = @.VesselCode
> > and t.ExpVoyagecode = @.VoyageCode
> > and t.LinerCode = @.LinerCode
> > insert #mytemp
> > select distinct
> > c.ContainerNo etc...
> > from trnExpContainerDetail c
> > where C.LinerCode = @.LinerCode
> > and C.VesselCode = @.VesselCode
> > and C.VoyageCode = @.VoyageCode
> > and C.BLnumber = @.BLnumber
> > and not exists (select 1 from #my_temp where #my_temp. ContainerNo => > c. ContainerNo
> > and #my_temp. ContainerType = c. ContainerType and #my_temp.
> > BookingNote = c. BookingNote
> > and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> > ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> > ReleaseRefNo )
> > That should move the timetable to less than 5 minutes to run. I think
> > that is acceptable. You can fine tune it further by using more #temp
> > tables for inner subqueries and joining them with #my_temp using
> > updates statements.- Hide quoted text -
> - Show quoted text -
Dan I don't need schema. If I need one I will ask tl from you. Thanks.|||With all due respect SB, if you want to provide real help to the OP you DO
need schema, indexes and possibly even data distribution information. Your
stab in the dark could well be WORSE performing due the large I/O overhead
associated with temporary tables (and the sproc recompiles associated with
them too). Even worse is that it completely avoids the possibility that a
single appropriate index addition could improve the sproc performance 1-4
orders of magnitude.
We on this forum should strive to give posters the right tools to help them
in the future as well as simply attempting to solve their current problem.
With just a bit more information from the OP we could have a dialog that
provided good mentoring to all while providing the BEST solution to Nitu.
--
TheSQLGuru
President
Indicium Resources, Inc.
"SB" <othellomy@.yahoo.com> wrote in message
news:1185852523.248498.24130@.d55g2000hsg.googlegroups.com...
> On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
>> > That should move the timetable to less than 5 minutes to run. I think
>> > that is acceptable. You can fine tune it further by using more #temp
>> > tables for inner subqueries and joining them with #my_temp using
>> > updates statements.
>> SB, how can you make these recommendations without knowledge of the
>> underlying schema? I'm not saying that employing a temp tables won't
>> improve performance, it's just that I've often seen temp tables used as a
>> kludge workaround when the real issue is lack of useful indexes and
>> non-saragable query expressions. IMHO, a better approach is to
>> understand
>> and address the underlying cause for the poor performance.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "SB" <othell...@.yahoo.com> wrote in message
>> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>>
>> > On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
>> >> I want to change following store procedure, it takes around 5 minutes
>> >> to
>> >> execute & I calls for each vessel / voyage combination... , Could any
>> >> one
>> >> help me to fine tune this store procedure.
>> >> --
>> >> CREATE PROCEDURE get_ContainerListinExpBL
>> >> @.VesselCode varchar(10) ,
>> >> @.VoyageCode varchar(10),
>> >> @.LinerCode varchar(10) ,
>> >> @.BookingList varchar(1000) = null ,
>> >> @.BLNumber varchar(20) = null
>> >> AS
>> >> select distinct t.ContainerNo,
>> >> T.ContainerType,
>> >> (select BookingNote
>> >> from trnActivity TA
>> >> inner join trnReleasenote RA on
>> >> RA.ReleaseNoteCode = TA.DO and RA.Liner =>> >> TA.LinerCode
>> >> where TA.ContainerNo = T.ContainerNo
>> >> and TA.LinerCode = t.LinerCode
>> >> and isnull(TA.VesselCode,'') =>> >> isnull(t.ImpVesselcode ,'')
>> >> and isnull(TA.VoyageCode, '') =>> >> isnull(t.ImpVoyageCode, '')
>> >> and DO is not Null
>> >> and TA.ActivityDate =>> >> (select Max(activityDate ) from
>> >> trnActivity TB
>> >> where TB.ContainerNo = t.ContainerNo
>> >> and TB.LinerCode = t.LinerCode
>> >> and isnull(TB.VesselCode,'') =>> >> isnull(t.ImpVesselCode , '')
>> >> and isnull(TB.VoyageCode, '') =>> >> isnull(t.ImpVoyageCode, '')
>> >> and TB.DO is not null)) BookingNote ,
>> >> t.ImpVesselCode ,
>> >> t.ImpVoyageCode ,
>> >> t.ReleaseRefNo
>> >> from trnExpTracking t
>> >> --inner join mstISO O on O.ContainerType = T.ContainerType
>> >> where t.ExpVesselCode = @.VesselCode
>> >> and t.ExpVoyagecode = @.VoyageCode
>> >> and t.LinerCode = @.LinerCode
>> >> union
>> >> select
>> >> c.ContainerNo,
>> >> c.ContainerType,
>> >> c.BookingNote,
>> >> c.ImpVesselCode ,
>> >> c.ImpVoyageCode ,
>> >> c.ReleaseRefNo
>> >> from trnExpContainerDetail c
>> >> where C.LinerCode = @.LinerCode
>> >> and C.VesselCode = @.VesselCode
>> >> and C.VoyageCode = @.VoyageCode
>> >> and C.BLnumber = @.BLnumber
>> >> GO
>> >> -- --
>> >> Thanks in advance
>> > Hi, try removing the union and use a temp table instead. For example:
>> > select distinct t.ContainerNo etc...
>> > into #my_temp
>> > from trnExpTracking t
>> > --inner join mstISO O on O.ContainerType = T.ContainerType
>> > where t.ExpVesselCode = @.VesselCode
>> > and t.ExpVoyagecode = @.VoyageCode
>> > and t.LinerCode = @.LinerCode
>> > insert #mytemp
>> > select distinct
>> > c.ContainerNo etc...
>> > from trnExpContainerDetail c
>> > where C.LinerCode = @.LinerCode
>> > and C.VesselCode = @.VesselCode
>> > and C.VoyageCode = @.VoyageCode
>> > and C.BLnumber = @.BLnumber
>> > and not exists (select 1 from #my_temp where #my_temp. ContainerNo =>> > c. ContainerNo
>> > and #my_temp. ContainerType = c. ContainerType and #my_temp.
>> > BookingNote = c. BookingNote
>> > and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
>> > ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
>> > ReleaseRefNo )
>> > That should move the timetable to less than 5 minutes to run. I think
>> > that is acceptable. You can fine tune it further by using more #temp
>> > tables for inner subqueries and joining them with #my_temp using
>> > updates statements.- Hide quoted text -
>> - Show quoted text -
> Dan I don't need schema. If I need one I will ask tl from you. Thanks.
>

Help for Fine Tune Store procedure to increase its performance

I want to change following store procedure, it takes around 5 minutes to
execute & I calls for each vessel / voyage combination... , Could any one
help me to fine tune this store procedure.
--
CREATE PROCEDURE get_ContainerListinExpBL
@.VesselCode varchar(10) ,
@.VoyageCode varchar(10),
@.LinerCode varchar(10) ,
@.BookingList varchar(1000) = null ,
@.BLNumber varchar(20) = null
AS
select distinct t.ContainerNo,
T.ContainerType,
(select BookingNote
from trnActivity TA
inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
TA.LinerCode
where TA.ContainerNo = T.ContainerNo
and TA.LinerCode = t.LinerCode
and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and DO is not Null
and TA.ActivityDate =
(select Max(activityDate ) from
trnActivity TB
where TB.ContainerNo = t.ContainerNo
and TB.LinerCode = t.LinerCode
and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
and TB.DO is not null)) BookingNote ,
t.ImpVesselCode ,
t.ImpVoyageCode ,
t.ReleaseRefNo
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
union
select
c.ContainerNo,
c.ContainerType,
c.BookingNote,
c.ImpVesselCode ,
c.ImpVoyageCode ,
c.ReleaseRefNo
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
GO
-- --
Thanks in advancePlease also post DDL (CREATE TABLE statements, including constraints and
indexes). The underlying schema can affect query performance significantly.
Hope this helps.
Dan Guzman
SQL Server MVP
"Nitu" <Nitu@.discussions.microsoft.com> wrote in message
news:05229BAA-ABA5-4C85-A298-616D82F629D3@.microsoft.com...
>I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode = TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesselcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselCode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVoyageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
>
> -- --
> Thanks in advance|||Nitu,
You should post the DDL, including all indexes, that is key. Also, ask
yourself if you really need to handle NULL values in VesselCode and/or
VoyageCode, because the use of ISNULL() will disallow the use of an
index for those columns.
Gert-Jan
Nitu wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode
= TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesse
lcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoy
ageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselC
ode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVo
yageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance|||On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> I want to change following store procedure, it takes around 5 minutes to
> execute & I calls for each vessel / voyage combination... , Could any one
> help me to fine tune this store procedure.
> --
> CREATE PROCEDURE get_ContainerListinExpBL
> @.VesselCode varchar(10) ,
> @.VoyageCode varchar(10),
> @.LinerCode varchar(10) ,
> @.BookingList varchar(1000) = null ,
> @.BLNumber varchar(20) = null
> AS
> select distinct t.ContainerNo,
> T.ContainerType,
> (select BookingNote
> from trnActivity TA
> inner join trnReleasenote RA on RA.ReleaseNoteCode
= TA.DO and RA.Liner =
> TA.LinerCode
> where TA.ContainerNo = T.ContainerNo
> and TA.LinerCode = t.LinerCode
> and isnull(TA.VesselCode,'') = isnull(t.ImpVesse
lcode ,'')
> and isnull(TA.VoyageCode, '') = isnull(t.ImpVoy
ageCode, '')
> and DO is not Null
> and TA.ActivityDate =
> (select Max(activityDate ) from
> trnActivity TB
> where TB.ContainerNo = t.ContainerNo
> and TB.LinerCode = t.LinerCode
> and isnull(TB.VesselCode,'') = isnull(t.ImpVesselC
ode , '')
> and isnull(TB.VoyageCode, '') = isnull(t.ImpVo
yageCode, '')
> and TB.DO is not null)) BookingNote ,
> t.ImpVesselCode ,
> t.ImpVoyageCode ,
> t.ReleaseRefNo
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> union
> select
> c.ContainerNo,
> c.ContainerType,
> c.BookingNote,
> c.ImpVesselCode ,
> c.ImpVoyageCode ,
> c.ReleaseRefNo
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> GO
> -- --
> Thanks in advance
Hi, try removing the union and use a temp table instead. For example:
select distinct t.ContainerNo etc...
into #my_temp
from trnExpTracking t
--inner join mstISO O on O.ContainerType = T.ContainerType
where t.ExpVesselCode = @.VesselCode
and t.ExpVoyagecode = @.VoyageCode
and t.LinerCode = @.LinerCode
insert #mytemp
select distinct
c.ContainerNo etc...
from trnExpContainerDetail c
where C.LinerCode = @.LinerCode
and C.VesselCode = @.VesselCode
and C.VoyageCode = @.VoyageCode
and C.BLnumber = @.BLnumber
and not exists (select 1 from #my_temp where #my_temp. ContainerNo =
c. ContainerNo
and #my_temp. ContainerType = c. ContainerType and #my_temp.
BookingNote = c. BookingNote
and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
ReleaseRefNo )
That should move the timetable to less than 5 minutes to run. I think
that is acceptable. You can fine tune it further by using more #temp
tables for inner subqueries and joining them with #my_temp using
updates statements.|||> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
SB, how can you make these recommendations without knowledge of the
underlying schema? I'm not saying that employing a temp tables won't
improve performance, it's just that I've often seen temp tables used as a
kludge workaround when the real issue is lack of useful indexes and
non-saragable query expressions. IMHO, a better approach is to understand
and address the underlying cause for the poor performance.
Hope this helps.
Dan Guzman
SQL Server MVP
"SB" <othellomy@.yahoo.com> wrote in message
news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
> On Jul 28, 2:14 pm, Nitu <N...@.discussions.microsoft.com> wrote:
> Hi, try removing the union and use a temp table instead. For example:
> select distinct t.ContainerNo etc...
> into #my_temp
> from trnExpTracking t
> --inner join mstISO O on O.ContainerType = T.ContainerType
> where t.ExpVesselCode = @.VesselCode
> and t.ExpVoyagecode = @.VoyageCode
> and t.LinerCode = @.LinerCode
> insert #mytemp
> select distinct
> c.ContainerNo etc...
> from trnExpContainerDetail c
> where C.LinerCode = @.LinerCode
> and C.VesselCode = @.VesselCode
> and C.VoyageCode = @.VoyageCode
> and C.BLnumber = @.BLnumber
> and not exists (select 1 from #my_temp where #my_temp. ContainerNo =
> c. ContainerNo
> and #my_temp. ContainerType = c. ContainerType and #my_temp.
> BookingNote = c. BookingNote
> and #my_temp. ImpVesselCode = c. ImpVesselCode and #my_temp.
> ImpVoyageCode = c. ImpVoyageCode and #my_temp. ReleaseRefNo = c.
> ReleaseRefNo )
>
> That should move the timetable to less than 5 minutes to run. I think
> that is acceptable. You can fine tune it further by using more #temp
> tables for inner subqueries and joining them with #my_temp using
> updates statements.
>|||On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> SB, how can you make these recommendations without knowledge of the
> underlying schema? I'm not saying that employing a temp tables won't
> improve performance, it's just that I've often seen temp tables used as a
> kludge workaround when the real issue is lack of useful indexes and
> non-saragable query expressions. IMHO, a better approach is to understand
> and address the underlying cause for the poor performance.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SB" <othell...@.yahoo.com> wrote in message
> news:1185777942.351695.318730@.q75g2000hsh.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Dan I don't need schema. If I need one I will ask tl from you. Thanks.|||With all due respect SB, if you want to provide real help to the OP you DO
need schema, indexes and possibly even data distribution information. Your
stab in the dark could well be WORSE performing due the large I/O overhead
associated with temporary tables (and the sproc recompiles associated with
them too). Even worse is that it completely avoids the possibility that a
single appropriate index addition could improve the sproc performance 1-4
orders of magnitude.
We on this forum should strive to give posters the right tools to help them
in the future as well as simply attempting to solve their current problem.
With just a bit more information from the OP we could have a dialog that
provided good mentoring to all while providing the BEST solution to Nitu.
TheSQLGuru
President
Indicium Resources, Inc.
"SB" <othellomy@.yahoo.com> wrote in message
news:1185852523.248498.24130@.d55g2000hsg.googlegroups.com...
> On Jul 30, 6:35 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
> Dan I don't need schema. If I need one I will ask tl from you. Thanks.
>

Help exporting data from .csv file to Sql server

Hi i am trying to export data from .csv file to sql server and my data is coming as

"xyz" where i want only to store as XYZ i am using derived column but i am not able to capture " and replace it what approach should i take here , also i am trying to convert String True to Boolean 1 and vice versa for False in database how do i do that please help me with this.

Look at the REPLACE function. Also, aren't you specifying (") as the text qualifier in your file setup?

As for converting the string True/False to boolean, you can use conditionals in a derived column. [Column] = "True" ? 1 : 0

You might just be able to cast the column, though I'm not sure. You'll have to test that out.

|||Big Thanks for the Help ... it works .|||

I am trying to store the value of True or False in a bit field in sql server which will be 1 for true and 0 false .. i am confused as to whether i need to cast it before storing it . please guide me.

From the Data Viewer i can see the Values changing to 1 and 0 but how do i store them as bit datatype in sql server

|||

Dev2624 wrote:

I am trying to store the value of True or False in a bit field in sql server which will be 1 for true and 0 false .. i am confused as to whether i need to cast it before storing it . please guide me.

From the Data Viewer i can see the Values changing to 1 and 0 but how do i store them as bit datatype in sql server

It should just work. If the values are 1s and 0s. Have you tried it!?|||I tried and it is storing as True in the column value but not as 1 in sql server , i am not aware of how exactly bit datatype works|||

Dev2624 wrote:

I tried and it is storing as True in the column value but not as 1 in sql server , i am not aware of how exactly bit datatype works

If the column's data type is "bit" it isn't storing as "True" or "False." However you're verifying the results, it's that application that's *presenting* the data as True/False. The underlying data is stored as 0 or 1.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1079022&SiteID=1|||It was a big help Thanks!!!

Help - Just using the time of DateTime or SmallDateTime

I need to store the time in a table ("10:00:00 AM") and then compare just
the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
this is simple as heck but because I am a newbie I am stumbling. Can
someone provide me with a sample of this? Thank you.What datatype is the column in the table of?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Code Boy" <CodeBoy@.microsoft.com> wrote in message news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.ph
x.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
> the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
> this is simple as heck but because I am a newbie I am stumbling. Can
> someone provide me with a sample of this? Thank you.
>|||That is up to me (so whatever you tell me). I just want to end up selecting
(with T-SQL) any row where a column has a time only greater then the time
portion of the current time stamp. Thank you.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMb8aRNVGHA.4740@.TK2MSFTNGP14.phx.gbl...
> What datatype is the column in the table of?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Code Boy" <CodeBoy@.microsoft.com> wrote in message
> news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...|||code
select convert(varchar(8),current_timestamp,108
) , it will give you
time.
You have to compare it with time in your table.
Look at convert function for more details in BOL.
Regards
Amish Shah.|||When I do this the compare to the time in my table does not select anyting.
It is as if now that they are varchars the >= operator does not work
properly. I will look in BOL for more info. Thank you.
"amish" <shahamishm@.gmail.com> wrote in message
news:1143820529.897848.99710@.g10g2000cwb.googlegroups.com...
> code
> select convert(varchar(8),current_timestamp,108
) , it will give you
> time.
> You have to compare it with time in your table.
> Look at convert function for more details in BOL.
> Regards
> Amish Shah.
>|||>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL.
Okay, so when you insert the data into SQL Server, make sure the date is not
included (or manually force it to be 1900-01-01 on insert).
Now, you can compare to CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE()). An
example:
CREATE TABLE #foo
(
FooID INT,
NextBar SMALLDATETIME
)
INSERT #foo SELECT 1, '1900-01-01 18:34';
INSERT #foo SELECT 2, '02:25'; -- 1900-01-01 is the default
INSERT #foo SELECT 3, '06:34';
INSERT #foo SELECT 4, '10:25';
INSERT #foo SELECT 5, '14:57';
INSERT #foo SELECT 6, '20:36';
INSERT #foo SELECT 6, '23:11';
-- now let's find those rows where NextBar is within the next 6 hours
DECLARE @.s SMALLDATETIME, @.e SMALLDATETIME;
SET @.s = CURRENT_TIMESTAMP - DATEDIFF(DAY, 0, GETDATE());
SET @.e = DATEADD(HOUR, 6, @.s);
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo
WHERE NextBar >= @.s
AND NextBar <= @.e;
-- see all rows:
SELECT FooID, NextBar = CONVERT(CHAR(5), NextBar, 108)
FROM #foo;
DROP TABLE #foo;|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||Figured it out:
select cast(convert(varchar,OurTime,114) as datetime) as OurTime
from FileName
where cast(convert(varchar,OurTime,114) as datetime) >
convert(varchar,getdate(),114)
Thank you all!
"Code Boy" <CodeBoy@.microsoft.com> wrote in message
news:e%23jVzKNVGHA.5092@.TK2MSFTNGP10.phx.gbl...
>I need to store the time in a table ("10:00:00 AM") and then compare just
>the time of the CURRENT_TIMESTAMP to it programmatically in T-SQL. I know
>this is simple as heck but because I am a newbie I am stumbling. Can
>someone provide me with a sample of this? Thank you.
>|||> select cast(convert(varchar,OurTime,114) as datetime) as OurTime
> from FileName
> where cast(convert(varchar,OurTime,114) as datetime) >
> convert(varchar,getdate(),114)
All these casts and converts will really hurt your performance.
If you're cunning enough to have an index on that column, you should compare
the plan for this solution compared to the one I provided...

Help - Best way to store related data (across or down)?

Good day.
I have a form with approx 90 related components. All 90 components are
associated with an App_ID. An App_ID can occur more than once but with
different data from the 90 fields.
I simply cannot decide whether to store the data from each component in
it's own column or as a separate row.
Any suggestions would be greatly appreciated.
Thanks!
*** Sent via Developersdex http://www.examnotes.net ***I think you hit on the key word here "related". To me that automatically
implies that you would set up a separate, but related, table to contain your
90 components. Without knowing more than you said about your app, I would
probably store each related component in its own row in the second table.
My assumption is that not all 90 components would necessarily be populated
for all App_ID's and that if, in the future, you add a 91st component you
don't necessarily want to change your whole schema.
Here's some example DDL. It contains 3 related tables, Apps which contains
your AppID and other App-specific info, AppComponents which contains an
App-specific component list, and LookupComponents which contains
Component-specific information (in this case just ComponentID and Name):
CREATE TABLE [dbo].[AppComponents] (
[AppID] [int] NOT NULL ,
[ComponentID] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[Apps] (
[AppID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
CREATE TABLE [dbo].[LookupComponents] (
[ComponentID] [int] NOT NULL ,
[Name] [varchar] (50) NULL
)
GO
ALTER TABLE [dbo].[AppComponents] WITH NOCHECK ADD
CONSTRAINT [PK_AppComponents] PRIMARY KEY CLUSTERED
(
[AppID],
[ComponentID]
)
GO
ALTER TABLE [dbo].[Apps] WITH NOCHECK ADD
CONSTRAINT [PK_Apps] PRIMARY KEY CLUSTERED
(
[AppID]
)
GO
ALTER TABLE [dbo].[LookupComponents] WITH NOCHECK ADD
CONSTRAINT [PK_LookupComponents] PRIMARY KEY CLUSTERED
(
[ComponentID]
)
GO
ALTER TABLE [dbo].[AppComponents] ADD
CONSTRAINT [FK_AppComponents_Apps] FOREIGN KEY
(
[AppID]
) REFERENCES [dbo].[Apps] (
[AppID]
),
CONSTRAINT [FK_AppComponents_LookupComponents] FOREIGN KEY
(
[ComponentID]
) REFERENCES [dbo].[LookupComponents] (
[ComponentID]
)
GO
"bajan_rick" <bajanrick@.devdex.com> wrote in message
news:eaMBbw1QFHA.4028@.tk2msftngp13.phx.gbl...
> Good day.
> I have a form with approx 90 related components. All 90 components are
> associated with an App_ID. An App_ID can occur more than once but with
> different data from the 90 fields.
>
> I simply cannot decide whether to store the data from each component in
> it's own column or as a separate row.
>
> Any suggestions would be greatly appreciated.
> Thanks!
>
> *** Sent via Developersdex http://www.examnotes.net ***|||>> I have a form with approx 90 related components. All 90 components
are associated with an App_ID. An App_ID can occur more than once but
with different data from the 90 fields. <<
Do not confuse the forms that collect data with the data model and the
tables in the schema. If they are all attributes of the same entity,
then they belong in one row in the table. If a subset of columns is
another entity, then it needs to be in its own table.
Then we have to ask if "app_id" is really a key or not. It sounds like
a sequential form number (remember the "Bates Numbering Stamp" in the
old days?) that has nothing to do with the data model, but is used for
data collection control.
Without any real information, it is imposible to give specific advice.
But a while a 90 column table is possible, it is rare. I would guess
-- I mean guess -- that this is really a 5 to 6 column schema.
The worst thing you can do is build an EAV design or some other variant
that mixes data and metadata in the name of a "flexible" design. They
fall aprt in about a year of production work after running insanely
slow.|||Thanks guys.
I'm not if I can explain the app anymore. I guess it's more like a
spreadsheet where there is the possiblit of multiple rows being
represented by 1 App_ID (PK).
My table is currently:
CREATE TABLE [dbo].[App_Sheet] (
[SheetID] [int] NOT NULL ,
[AppID] [int] NOT NULL ,
[Component_ID] [int],
[Value] [varchar] (50) NULL
)
GO
CELKO:
What do you mean by
"..this is really a 5 to 6 column schema"
*** Sent via Developersdex http://www.examnotes.net ***