|
Home > Archive > MS SQL Server > October 2006 > 2005 grows
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Microsoft 2006-10-24, 6:32 pm |
| Hi,
I have a server running 2005. I have a SP that dumps data into a table. When
it runs, it adds 100K records (200Mb?) but the database grows to 19GB! but
18.5 is just space. When I shrink the DB I goes back down to 500mb?
If I run the SP again, I get the same problem
Thanks for the help!
| |
| Andrew J. Kelly 2006-10-24, 6:32 pm |
| What does the table structure look like? What is the clustered index on? Is
there any text columns? How about showing the sp? All of these things would
help a great deal to get an accurate answer.
--
Andrew J. Kelly SQL MVP
"Microsoft" <g2@bla.cl> wrote in message
news:ej8gK204GHA.2288@TK2MSFTNGP05.phx.gbl...
> Hi,
> I have a server running 2005. I have a SP that dumps data into a table.
> When it runs, it adds 100K records (200Mb?) but the database grows to
> 19GB! but 18.5 is just space. When I shrink the DB I goes back down to
> 500mb?
> If I run the SP again, I get the same problem
>
> Thanks for the help!
>
>
| |
| Microsoft 2006-10-24, 6:32 pm |
| Text cols? yes.... Clustered index? no clue.
Here is the SP
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Conta
iner. Cargo_Shipment_Conta
iner_id,
Cargo_Shipment_Conta
iner.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Conta
iner. Tot_Container_Packag
es_Weight,
Cargo_Shipment_Conta
iner. Tot_Container_Weight
_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code
,
Charge. Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
comentarios = (select top 1
comment
from
mydb.. Cargo_Shipment_Comme
nt (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
),
comentario2 = (select
comment
from
mydb.. Cargo_Shipment_Comme
nt (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
and cargo_shipment_comme
nt_id =
(select top 1 cargo_shipment_comme
nt_id
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Conta
iner. cargo_shipment_id)+1
),
comentario3 = (select
comment
from
mydb.. Cargo_Shipment_Comme
nt (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
and cargo_shipment_comme
nt_id =
(select top 1 cargo_shipment_comme
nt_id
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Conta
iner. cargo_shipment_id)+2
),
comentario4 = (select
comment
from
mydb.. Cargo_Shipment_Comme
nt (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
and cargo_shipment_comme
nt_id =
(select top 1 cargo_shipment_comme
nt_id
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Conta
iner. cargo_shipment_id)+3
),
comentario5 = (select
comment
from
mydb.. Cargo_Shipment_Comme
nt (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
and cargo_shipment_comme
nt_id =
(select top 1 cargo_shipment_comme
nt_id
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Conta
iner. cargo_shipment_id)+4
),
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Conta
iner.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Conta
iner.Max_Temperature),
Cargo_Shipment_Conta
iner.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h. doc_company_class_id
= 2 -- 1 Shipper
and g. doc_company_class_id
= h. doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company. Doc_Company_Class_id
,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Conta
iner. Final_Dest_Location_
id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM
mydb..Charge_Definition Charge_Definition INNER JOIN
mydb.. Charge_Specific_Rule
Charge_Specific_Rule
ON
Charge_Definition.ChgDef_id = Charge_Specific_Rule
.ChgDef_id INNER JOIN
mydb..Charge Charge ON Charge_Specific_Rule
.ChgSpRule_id =
Charge.ChgSpRule_id INNER JOIN
mydb.. Cargo_Shipment_Conta
iner Cargo_Shipment_Conta
iner ON
Charge. Cargo_Shipment_Conta
iner_id =
Cargo_Shipment_Conta
iner. Cargo_Shipment_Conta
iner_id INNER JOIN
mydb..Cargo Cargo ON
Cargo_Shipment_Conta
iner. Cargo_Shipment_Conta
iner_id =
Cargo. Cargo_Shipment_Conta
iner_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Conta
iner.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id = GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Conta
iner.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Conta
iner. Final_Dest_Location_
id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Conta
iner.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Conta
iner. Pick_Up_Empty_Depot_
id = GISDepot_EDI.Depot_id LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:u1ZULv24GHA.3732@TK2MSFTNGP05.phx.gbl...
> What does the table structure look like? What is the clustered index on?
> Is there any text columns? How about showing the sp? All of these things
> would help a great deal to get an accurate answer.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Microsoft" <g2@bla.cl> wrote in message
> news:ej8gK204GHA.2288@TK2MSFTNGP05.phx.gbl...
>
>
| |
| Andrew J. Kelly 2006-10-24, 6:33 pm |
| Is tempdb the one growing or the user database? Is it the data file or the
log file? You show that you are doing an select into
> into
> ETL_TBL_Charge
Is this true or was the code simplified and you are really doing an Insert
Into instead? If it is a Select Into have you tried settign the recovery
mode to Bulk logged or Simple mode?
--
Andrew J. Kelly SQL MVP
"Microsoft" <g2@bla.cl> wrote in message
news:eYZcaD34GHA.1188@TK2MSFTNGP05.phx.gbl...
> Text cols? yes.... Clustered index? no clue.
> Here is the SP
>
> SELECT DISTINCT
> Vessel_Search.Name as Vessel_Name,
> Vessel_Search.Vessel_Search_id,
> Voyage_Search.Voyage_Number,
> Voyage_Search.Voyage_Id,
> Voyage_Search.Voyage_Search_Id,
> Service.Code,
> Service.Description as Service_Description,
> GISPort.Local_Name,
> Cargo_Shipment.Booking_Number,
> GISCompany.Global_Name,
> GISEquipment_Type.Abbreviation,
> Cargo.Commodity_id,
> GISCommodity.Name,
> Cargo_Shipment_Conta
iner. Cargo_Shipment_Conta
iner_id,
> Cargo_Shipment_Conta
iner.Shipment_Method_id,
> Cargo.Quantity,
> Cargo.Weight,
> Cargo.Weight_Unit,
> Cargo_Shipment_Conta
iner. Tot_Container_Packag
es_Weight,
> Cargo_Shipment_Conta
iner. Tot_Container_Weight
_Unit,
> GISPort_EDI.EDI_Code as EDI_Code,
> GISPort_EDI_1.EDI_Code as EDI_Code1,
> GISLocation_EDI.EDI_Code GISLocation_EDI_Code
,
> Charge. Adjust_Payment_Type,
> Charge.Adjust_Param_Std,
> GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
> comentarios = (select top 1
> comment
> from
> mydb.. Cargo_Shipment_Comme
nt (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
> ),
>
> comentario2 = (select
> comment
> from
> mydb.. Cargo_Shipment_Comme
nt (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
> and cargo_shipment_comme
nt_id =
> (select top 1 cargo_shipment_comme
nt_id
> from mydb.. Cargo_Shipment_Comme
nt (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Conta
iner. cargo_shipment_id)+1
),
>
> comentario3 = (select
> comment
> from
> mydb.. Cargo_Shipment_Comme
nt (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
> and cargo_shipment_comme
nt_id =
> (select top 1 cargo_shipment_comme
nt_id
> from mydb.. Cargo_Shipment_Comme
nt (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Conta
iner. cargo_shipment_id)+2
),
>
> comentario4 = (select
> comment
> from
> mydb.. Cargo_Shipment_Comme
nt (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
> and cargo_shipment_comme
nt_id =
> (select top 1 cargo_shipment_comme
nt_id
> from mydb.. Cargo_Shipment_Comme
nt (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Conta
iner. cargo_shipment_id)+3
),
>
> comentario5 = (select
> comment
> from
> mydb.. Cargo_Shipment_Comme
nt (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
> and cargo_shipment_comme
nt_id =
> (select top 1 cargo_shipment_comme
nt_id
> from mydb.. Cargo_Shipment_Comme
nt (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Conta
iner. cargo_shipment_id)+4
),
>
> Hazardous_Cargo.IMO_Class_Number,
> Code.Description as Code_Description,
> Min_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Conta
iner.Min_Temperature),
> Max_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Conta
iner.Max_Temperature),
> Cargo_Shipment_Conta
iner.Temperature_Unit,
> nombre_Ffwd = ( select top 1 j.Global_Name
> from
> mydb..assoc_doc_company g (nolock),
> mydb..doc_company_class h (nolock),
> mydb..GISCompany_Address i (nolock),
> mydb..GIScompany j (nolock)
> where
> GISCompany_Address.company_id = j.company_id
> and i.company_id = j.company_id
> and g.company_address_id = i.company_address_id
> and h. doc_company_class_id
= 2 -- 1 Shipper
> and g. doc_company_class_id
= h. doc_company_class_id
> ),
> Charge_Definition.DTX_Code,
> Cargo_Shipment.Status_Code_id,
> Assoc_Doc_Company. Doc_Company_Class_id
,
> Schedule_Leg.Load_Port_id,
> Schedule_Leg.Discharge_Port_id,
> Cargo_Shipment_Conta
iner. Final_Dest_Location_
id,
> GISCompany_Address.Company_id
>
> into
> ETL_TBL_Charge
>
> FROM
> mydb..Charge_Definition Charge_Definition INNER JOIN
> mydb.. Charge_Specific_Rule
Charge_Specific_Rule
ON
> Charge_Definition.ChgDef_id = Charge_Specific_Rule
.ChgDef_id INNER JOIN
> mydb..Charge Charge ON Charge_Specific_Rule
.ChgSpRule_id =
> Charge.ChgSpRule_id INNER JOIN
> mydb.. Cargo_Shipment_Conta
iner Cargo_Shipment_Conta
iner ON
> Charge. Cargo_Shipment_Conta
iner_id =
> Cargo_Shipment_Conta
iner. Cargo_Shipment_Conta
iner_id INNER JOIN
> mydb..Cargo Cargo ON
> Cargo_Shipment_Conta
iner. Cargo_Shipment_Conta
iner_id =
> Cargo. Cargo_Shipment_Conta
iner_id INNER JOIN
> mydb..Cargo_Shipment Cargo_Shipment ON
> Cargo_Shipment_Conta
iner.Cargo_Shipment_id =
> Cargo_Shipment.Cargo_Shipment_id INNER JOIN
> mydb..Assoc_Doc_Company Assoc_Doc_Company ON
> Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
> INNER JOIN
> mydb..GISCompany_Address GISCompany_Address ON
> Assoc_Doc_Company.Company_Address_id =
> GISCompany_Address.Company_Address_id INNER JOIN
> mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
> GISCompany.Company_id INNER JOIN
> mydb..Schedule_Leg Schedule_Leg ON
> Cargo_Shipment_Conta
iner.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
> INNER JOIN
> mydb..GISLocation_EDI GISLocation_EDI ON
> Cargo_Shipment_Conta
iner. Final_Dest_Location_
id =
> GISLocation_EDI.Location_id INNER JOIN
> mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id LEFT
> OUTER JOIN
> mydb..GISEquipment_Type GISEquipment_Type ON
> Cargo_Shipment_Conta
iner.Container_Type_id =
> GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
> mydb..GISDepot_EDI GISDepot_EDI ON
> Cargo_Shipment_Conta
iner. Pick_Up_Empty_Depot_
id = GISDepot_EDI.Depot_id
> LEFT OUTER JOIN
> mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
> GISCommodity.Commodity_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
> GISPort_EDI_1.Port_id LEFT OUTER JOIN
> mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
> GISPort_EDI.Port_id LEFT OUTER JOIN
> mydb..Service Service ON Cargo_Shipment.Service_id = Service.Service_id
> LEFT OUTER JOIN
> mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id = GISPort.Port_id
> LEFT OUTER JOIN
> mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
> Hazardous_Cargo.Cargo_id --,
>
> inner join
> mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
> voyage_Search.Voyage_id
> inner join
> mydb..Vessel_Search Vessel_Search on
> Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
> WHERE
> GISPort_EDI.EDI_Code_type_id =4
>
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:u1ZULv24GHA.3732@TK2MSFTNGP05.phx.gbl...
>
>
| |
| Microsoft 2006-10-24, 6:33 pm |
| Its a User Database.
I left this and a few other queries ruuning last night and I got this error
The transaction log for database 'mydb_Temp90' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Yes, Its doing the
Select xxx xxxx
into
table
From
bla bla bla
I am not familiar with the Bulk logged or simple mode.
Thanks.
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:%23bMScB84GHA.4256@TK2MSFTNGP03.phx.gbl...
> Is tempdb the one growing or the user database? Is it the data file or the
> log file? You show that you are doing an select into
>
> Is this true or was the code simplified and you are really doing an Insert
> Into instead? If it is a Select Into have you tried settign the recovery
> mode to Bulk logged or Simple mode?
>
> --
> Andrew J. Kelly SQL MVP
>
| |
| Andrew J. Kelly 2006-10-24, 6:33 pm |
| Those are recovery modes for the database and affect the amount of logging
for operations such as a Select Into. I recommend you change the recovery
mode to Simple when you do this operation. Just make sure you do a valid
FULL backup after if everything went correctly. You can find more info in
BooksOnLine. The log file can't grow fast enough to meet the needs of your
operations so you should either leave it that large, change the recovery
mode or change the default autogrow size to a fixed amount that can be
achieved in 10 seconds or less.
--
Andrew J. Kelly SQL MVP
"Microsoft" <g2@bla.cl> wrote in message
news:Op4dvL84GHA.2264@TK2MSFTNGP02.phx.gbl...
> Its a User Database.
> I left this and a few other queries ruuning last night and I got this
> error
> The transaction log for database 'mydb_Temp90' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Yes, Its doing the
> Select xxx xxxx
> into
> table
> From
> bla bla bla
>
> I am not familiar with the Bulk logged or simple mode.
>
> Thanks.
>
>
>
>
> "Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
> news:%23bMScB84GHA.4256@TK2MSFTNGP03.phx.gbl...
>
>
| |
| Microsoft 2006-10-24, 6:33 pm |
| Andrew,
I cant change this because I am not the owner of the db. All I can do is
read and write to tables, but cant change settings.
I did not write the SP and I have never seen an Insert done this way. I
would have done a tradicional Insert into table Select bla bla bla
If I change the SP would it have any impact?
George.
"Andrew J. Kelly" < sqlmvpnooospam@shadh
awk.com> wrote in message
news:uNvkXW94GHA.3376@TK2MSFTNGP05.phx.gbl...
> Those are recovery modes for the database and affect the amount of logging
> for operations such as a Select Into. I recommend you change the recovery
> mode to Simple when you do this operation. Just make sure you do a valid
> FULL backup after if everything went correctly. You can find more info in
> BooksOnLine. The log file can't grow fast enough to meet the needs of
> your operations so you should either leave it that large, change the
> recovery mode or change the default autogrow size to a fixed amount that
> can be achieved in 10 seconds or less.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Microsoft" <g2@bla.cl> wrote in message
> news:Op4dvL84GHA.2264@TK2MSFTNGP02.phx.gbl...
>
>
| |
| wbpelen@yahoo.com 2006-10-24, 6:33 pm |
| George,
You may consider re-writing the query - I tend to avoid nested selects
whenever possible and I would guess that your SP takes a long time to
run because it builds all those subselects into temporary space.
Consider rewriting your query - I put one version below.
SELECT DISTINCT
Vessel_Search.Name as Vessel_Name,
Vessel_Search.Vessel_Search_id,
Voyage_Search.Voyage_Number,
Voyage_Search.Voyage_Id,
Voyage_Search.Voyage_Search_Id,
Service.Code,
Service.Description as Service_Description,
GISPort.Local_Name,
Cargo_Shipment.Booking_Number,
GISCompany.Global_Name,
GISEquipment_Type.Abbreviation,
Cargo.Commodity_id,
GISCommodity.Name,
Cargo_Shipment_Conta
iner. Cargo_Shipment_Conta
iner_id,
Cargo_Shipment_Conta
iner.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Conta
iner. Tot_Container_Packag
es_Weight,
Cargo_Shipment_Conta
iner. Tot_Container_Weight
_Unit,
GISPort_EDI.EDI_Code as EDI_Code,
GISPort_EDI_1.EDI_Code as EDI_Code1,
GISLocation_EDI.EDI_Code GISLocation_EDI_Code
,
Charge. Adjust_Payment_Type,
Charge.Adjust_Param_Std,
GISDepot_EDI.EDI_Code as GISDepot_EDI_Code,
--**********Replace this section
********************
********************
******************
/*
comentarios = ( select top 1 comment
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
),
comentario2 = ( select comment
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment
_id =
Cargo_Shipment_Conta
iner.cargo_shipment_id
and cargo_shipment_comme
nt_id = (select top 1
cargo_shipment_comme
nt_id
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Conta
iner. cargo_shipment_id)+1
),
comentario3 = (select comment
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
and cargo_shipment_comme
nt_id = (select top 1
cargo_shipment_comme
nt_id
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Conta
iner. cargo_shipment_id)+2
),
comentario4 = (select comment
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment
_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
and cargo_shipment_comme
nt_id =
(select top 1 cargo_shipment_comme
nt_id
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Conta
iner. cargo_shipment_id)+3
),
comentario5 = (select comment
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment
_id = Cargo_Shipment_Conta
iner.cargo_shipment_id
and cargo_shipment_c
omment_id =
(select top 1 cargo_shipment_comme
nt_id
from mydb.. Cargo_Shipment_Comme
nt (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Conta
iner. cargo_shipment_id)+4
),
*/
--*************With This
********************
********************
********************
********************
**
comentarios = comment1.comment
comentario2 = comment2.comment
comentario3 = comment3.comment
comentario4 = comment4.comment
comentario5 = comment5.comment
-- ********************
********************
********************
********************
********************
******
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Conta
iner.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Conta
iner.Max_Temperature),
Cargo_Shipment_Conta
iner.Temperature_Unit,
nombre_Ffwd = ( select top 1 j.Global_Name
from
mydb..assoc_doc_company g (nolock),
mydb..doc_company_class h (nolock),
mydb..GISCompany_Address i (nolock),
mydb..GIScompany j (nolock)
where
GISCompany_Address.company_id = j.company_id
and i.company_id = j.company_id
and g.company_address_id = i.company_address_id
and h. doc_company_class_id
= 2 -- 1 Shipper
and g. doc_company_class_id
= h. doc_company_class_id
),
Charge_Definition.DTX_Code,
Cargo_Shipment.Status_Code_id,
Assoc_Doc_Company. Doc_Company_Class_id
,
Schedule_Leg.Load_Port_id,
Schedule_Leg.Discharge_Port_id,
Cargo_Shipment_Conta
iner. Final_Dest_Location_
id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROM mydb..Charge_Definition Charge_Definition
INNER JOIN mydb.. Charge_Specific_Rule
Charge_Specific_Rule
ON Charge_Definition
.ChgDef_id = Charge_Specific_Rule
.ChgDef_id
INNER JOIN mydb..Charge Charge
ON Charge_Specific_Rule
.ChgSpRule_id = Charge.ChgSpRule_id
INNER JOIN mydb.. Cargo_Shipment_Conta
iner Cargo_Shipment_Conta
iner
ON Charge. Cargo_Shipment_Conta
iner_id =
Cargo_Shipment_Conta
iner. Cargo_Shipment_Conta
iner_id
--*************I added This
********************
********************
**********
--Build a list of distinct shipment_id values with their minimum
comment id
--This list can be used as the baseline to add 1,2,3,4 to get to
subsequent comments
--It avoids the subselects within the main select statement.
LEFT OUTER JOIN ( SELECT cargo_shipm
ent_id,
min(cargo_shipment_c
omment_id) min_shipment_comment
_id
FROM mydb.. Cargo_Shipment_Comme
nt (nolock)
GROUP BY cargo_shipment_id ) shipment_comments
ON Cargo_Shipment_Co
ntainer.cargo_shipment_id =
shipment_comments.cargo_shipment_id
LEFT OUTER JOIN mydb.. Cargo_Shipment_Comme
nt (nolock) comment1
ON shipment_comments
.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comment
s. cargo_shipment_comme
nt_id =
min_shipment_comment
_id
LEFT OUTER JOIN mydb.. Cargo_Shipment_Comme
nt (nolock) comment2
ON shipment_comments
.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comment
s. cargo_shipment_comme
nt_id =
min_shipment_comment
_id + 1
LEFT OUTER JOIN mydb.. Cargo_Shipment_Comme
nt (nolock) comment3
ON shipment_comments
.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comment
s. cargo_shipment_comme
nt_id =
min_shipment_comment
_id + 2
LEFT OUTER JOIN mydb.. Cargo_Shipment_Comme
nt (nolock) comment4
ON shipment_comments
.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comment
s. cargo_shipment_comme
nt_id =
min_shipment_comment
_id + 3
LEFT OUTER JOIN mydb.. Cargo_Shipment_Comme
nt (nolock) comment5
ON shipment_comments
.cargo_shipment_id = comment1.cargo_shipment_id
AND shipment_comment
s. cargo_shipment_comme
nt_id =
min_shipment_comment
_id + 4
--**************Up to this point
********************
********************
************
INNER JOIN mydb..Cargo Cargo
ON Cargo_Shipment_Co
ntainer. Cargo_Shipment_Conta
iner_id =
Cargo. Cargo_Shipment_Conta
iner_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Conta
iner.Cargo_Shipment_id =
Cargo_Shipment.Cargo_Shipment_id INNER JOIN
mydb..Assoc_Doc_Company Assoc_Doc_Company ON
Cargo_Shipment.Cargo_Shipment_id = Assoc_Doc_Company.Cargo_Shipment_id
INNER
JOIN
mydb..GISCompany_Address GISCompany_Address ON
Assoc_Doc_Company.Company_Address_id =
GISCompany_Address.Company_Address_id
INNER JOIN
mydb..GISCompany GISCompany ON GISCompany_Address.Company_id =
GISCompany.Company_id INNER JOIN
mydb..Schedule_Leg Schedule_Leg ON
Cargo_Shipment_Conta
iner.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Conta
iner. Final_Dest_Location_
id =
GISLocation_EDI.Location_id INNER JOIN
mydb..Code Code ON Cargo_Shipment.Status_Code_id = Code.Code_id
LEFT
OUTER JOIN
mydb..GISEquipment_Type GISEquipment_Type ON
Cargo_Shipment_Conta
iner.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Conta
iner. Pick_Up_Empty_Depot_
id = GISDepot_EDI.Depot_id
LEFT
OUTER JOIN
mydb..GISCommodity GISCommodity ON Cargo.Commodity_id =
GISCommodity.Commodity_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI_1 ON Schedule_Leg.Discharge_Port_id =
GISPort_EDI_1.Port_id LEFT OUTER JOIN
mydb..GISPort_EDI GISPort_EDI ON Schedule_Leg.Load_Port_id =
GISPort_EDI.Port_id LEFT OUTER JOIN
mydb..Service Service ON Cargo_Shipment.Service_id =
Service.Service_id
LEFT OUTER JOIN
mydb..GISPort GISPort ON Schedule_Leg.Load_Port_id =
GISPort.Port_id
LEFT OUTER JOIN
mydb..Hazardous_Cargo Hazardous_Cargo ON Cargo.Cargo_id =
Hazardous_Cargo.Cargo_id --,
inner join
mydb..Voyage_Search Voyage_Search on schedule_leg.voyage_id =
voyage_Search.Voyage_id
inner join
mydb..Vessel_Search Vessel_Search on
Vessel_Search.Vessel_Search_id = Voyage_Search.Vessel_Search_id
WHERE
GISPort_EDI.EDI_Code_type_id =4
|
|
|
|
|