2005 grows

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!
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!
>
|||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_Container.Cargo_Shipment_Container_ id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_We ight,
Cargo_Shipment_Container.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_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (select
comment
from
mydb..Cargo_Shipment_Comment (nolock)
where
cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+4),
Hazardous_Cargo.IMO_Class_Number,
Code.Description as Code_Description,
Min_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.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_Container.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_Container Cargo_Shipment_Container ON
Charge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_ id INNER JOIN
mydb..Cargo Cargo ON
Cargo_Shipment_Container.Cargo_Shipment_Container_ id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.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_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.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_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.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@.shadhawk.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...
>
|||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_Container.Cargo_Shipment_Container_ id,
> Cargo_Shipment_Container.Shipment_Method_id,
> Cargo.Quantity,
> Cargo.Weight,
> Cargo.Weight_Unit,
> Cargo_Shipment_Container.Tot_Container_Packages_We ight,
> Cargo_Shipment_Container.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_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> ),
> comentario2 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+1),
> comentario3 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+2),
> comentario4 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+3),
> comentario5 = (select
> comment
> from
> mydb..Cargo_Shipment_Comment (nolock)
> where
> cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
> and cargo_shipment_comment_id =
> (select top 1 cargo_shipment_comment_id
> from mydb..Cargo_Shipment_Comment (nolock)
> where cargo_Shipment_id =
> Cargo_Shipment_Container.cargo_shipment_id)+4),
> Hazardous_Cargo.IMO_Class_Number,
> Code.Description as Code_Description,
> Min_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Min_Temperature),
> Max_Temperature = CONVERT(varchar(8),
> Cargo_Shipment_Container.Max_Temperature),
> Cargo_Shipment_Container.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_Container.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_Container Cargo_Shipment_Container ON
> Charge.Cargo_Shipment_Container_id =
> Cargo_Shipment_Container.Cargo_Shipment_Container_ id INNER JOIN
> mydb..Cargo Cargo ON
> Cargo_Shipment_Container.Cargo_Shipment_Container_ id =
> Cargo.Cargo_Shipment_Container_id INNER JOIN
> mydb..Cargo_Shipment Cargo_Shipment ON
> Cargo_Shipment_Container.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_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
> INNER JOIN
> mydb..GISLocation_EDI GISLocation_EDI ON
> Cargo_Shipment_Container.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_Container.Container_Type_id =
> GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
> mydb..GISDepot_EDI GISDepot_EDI ON
> Cargo_Shipment_Container.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@.shadhawk.com> wrote in message
> news:u1ZULv24GHA.3732@.TK2MSFTNGP05.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@.shadhawk.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
>
|||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@.shadhawk.com> wrote in message
> news:%23bMScB84GHA.4256@.TK2MSFTNGP03.phx.gbl...
>
|||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@.shadhawk.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...
>
|||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_Container.Cargo_Shipment_Container_ id,
Cargo_Shipment_Container.Shipment_Method_id,
Cargo.Quantity,
Cargo.Weight,
Cargo.Weight_Unit,
Cargo_Shipment_Container.Tot_Container_Packages_We ight,
Cargo_Shipment_Container.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_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
),
comentario2 = (select comment
frommydb..Cargo_Shipment_Comment (nolock)
wherecargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+1),
comentario3 = (select comment
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id = (select top 1
cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+2),
comentario4 = (select comment
frommydb..Cargo_Shipment_Comment (nolock)
wherecargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
and cargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.cargo_shipment_id)+3),
comentario5 = (selectcomment
frommydb..Cargo_Shipment_Comment (nolock)
wherecargo_Shipment_id = Cargo_Shipment_Container.cargo_shipment_id
andcargo_shipment_comment_id =
(select top 1 cargo_shipment_comment_id
from mydb..Cargo_Shipment_Comment (nolock)
where cargo_Shipment_id =
Cargo_Shipment_Container.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_Container.Min_Temperature),
Max_Temperature = CONVERT(varchar(8),
Cargo_Shipment_Container.Max_Temperature),
Cargo_Shipment_Container.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_Container.Final_Dest_Location_id,
GISCompany_Address.Company_id
into
ETL_TBL_Charge
FROMmydb..Charge_Definition Charge_Definition
INNER JOIN mydb..Charge_Specific_Rule Charge_Specific_Rule
ONCharge_Definition.ChgDef_id = Charge_Specific_Rule.ChgDef_id
INNER JOIN mydb..Charge Charge
ON Charge_Specific_Rule.ChgSpRule_id = Charge.ChgSpRule_id
INNER JOINmydb..Cargo_Shipment_Container Cargo_Shipment_Container
ONCharge.Cargo_Shipment_Container_id =
Cargo_Shipment_Container.Cargo_Shipment_Container_ 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 (SELECTcargo_shipment_id,
min(cargo_shipment_comment_id) min_shipment_comment_id
FROMmydb..Cargo_Shipment_Comment (nolock)
GROUP BY cargo_shipment_id ) shipment_comments
ONCargo_Shipment_Container.cargo_shipment_id=
shipment_comments.cargo_shipment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment1
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment2
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 1
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment3
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 2
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment4
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 3
LEFT OUTER JOIN mydb..Cargo_Shipment_Comment (nolock) comment5
ONshipment_comments.cargo_shipment_id= comment1.cargo_shipment_id
ANDshipment_comments.cargo_shipment_comment_id=
min_shipment_comment_id + 4
--**************Up to this point
************************************************** **
INNER JOINmydb..Cargo Cargo
ONCargo_Shipment_Container.Cargo_Shipment_Container_ id =
Cargo.Cargo_Shipment_Container_id INNER JOIN
mydb..Cargo_Shipment Cargo_Shipment ON
Cargo_Shipment_Container.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_Container.Schedule_Leg_id = Schedule_Leg.Schedule_Leg_id
INNER JOIN
mydb..GISLocation_EDI GISLocation_EDI ON
Cargo_Shipment_Container.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_Container.Container_Type_id =
GISEquipment_Type.Equipment_Type_id LEFT OUTER JOIN
mydb..GISDepot_EDI GISDepot_EDI ON
Cargo_Shipment_Container.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