Hi,
The ssis package takes 5 seconds to complete when run from the designer.
This ssis package is now triggered from within a scheduled job which takes 30 seconds to complete.
I do not see why there is a difference of 25 seconds.
Any thoughts please?
Thanks
Hi,
The ssis package takes 5 seconds to complete when run from the designer.
This ssis package is now triggered from within a scheduled job which takes 30 seconds to complete.
I do not see why there is a difference of 25 seconds.
Any thoughts please?
Thanks
Hi,
I'm trying to load 8mm records in SCD type 2 format. the time taken to load these records are very high all most two days with Intel Core 2 processor and 1gb ram
the direct load of 8mm records had taken time up to 12 minutes
can any one guide me up on the memory required for the job to success and is there any performance tuning have to be taken care in mapping.
Please do inform me up on this issue ASAP. Thank you
Hi,
I'm trying to create a package with SSIS to replace the DTS process that we have in place already.
DTS package copy four table content from one server to another. I have created a simple SSIS to do the same processes but the process it alot slower than DTS!!
I did ran the SSIS package using ctrl+F5 and also from command prompt but still it's quite slow.
SSIS uses SMO to access to server and both are running on 2005
Thanks
I would like to know how long will my package take to run if i have 2 million records to be inserted to my database.
Its taking hours( arround 5 hours or more)...is that the way it is????? i used oledb destinations..
i am transfering from text file to sql server database.
And one more thing which i want to know is...
Is is better to have several data flows where in one i check to see if the incoming records are valid.and have my insert or update logic in the other.
Will this increase the speed..
Hi,
I have several data bases on a server (SQL Server 2000 only, no web server installed) and lately, as the company keeps gowing, my users complain saying the server gets slow, (this dbs are well designed and recieve optimizations and integrity checks, etc) because of this, Im thinking about getting a new server to repleace my old ProLiant ML 330 which was bought 4 years ago but Im concerned about what server arquitecture or characteristic can help me best to improve response performance, is it HD speed? Processor speed? or more Ram? I want to make a good decision, so I´d really appreciate your help...
Thanks, Luis Luevano
Andy writes "I have data with more than 4 milions. How to speed up query it ?"
View 3 Replies View Relatedhello,
i need some opinion on how to sum up or group by more than 2k records faster.. eg, how do i optimize this?
SELECT DISTINCT r.ClientID,c.ClientName, r.ItemID, r.StockID,r.StockName, r.ExpectedQty,r.QCQty,r.AVAQty,r.PNDQty as pnd, r.VMIQCQty,r.VMIAVAQty,r.VMIPNDQty as vmipnd,
(Select isnull( SUM(d.HoldQty) ,0) FROM tblItemdetail d WHERE d.itemid=r.itemid AND d.ConsignorID=@ClientID AND d.Ownerstatus='VMI') AS VMIPNDQty,
(Select isnull( SUM(d.HoldQty) ,0) FROM tblItemdetail d WHERE d.itemid=r.itemid AND d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='VMI') AS PNDQty,
(Select isnull(SUM(d.OriginQty - d.PickQty -d.HoldQty -d.qcqty),0) FROM tblItemDetail d WHERE d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='OWN') AS StockAtCustAVAQty,
(Select isnull(SUM(d.HoldQty),0) FROM tblItemDetail d WHERE d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='OWN') AS StockAtCustPNDQty,
(Select isnull(SUM(d.qcqty),0) FROM tblItemDetail d WHERE d.ConsignorID=@ClientID AND d.Ownership= i.Supplier AND d.Ownerstatus='OWN') AS StockAtCustQCQty
FROM tblItemCrossRef r
INNER JOIN tblClient c ON c.ClientID=r.ClientID
INNER JOIN tblItemClients i on i.Supplier=r.ClientID
WHERE r.ClientID=@ClientID AND r.StockID LIKE @StockID+'%'
~~~Focus on problem, not solution~~~
Hello all-Given the following UDF, in sql 2000 can it be sped up, complied oranything of the like. A query returning 300,000 + rows times out whenran through the udf, inline case statements returns the rows in 5seconds.Thanks!JeffCREATE FUNCTION dbo.TimeFormat(@input datetime,@groupformatvarchar(20) --DAY, WEEK, MONTH)RETURNS datetimeASBEGINdeclare @dtvar as datetimeif @groupformat = 'DAY'set @dtvar = CAST(CONVERT(char(10), @input, 101) AS datetime)else if @groupformat = 'WEEK'set @dtvar = CAST(DATEADD([DAY], 1 - DATEPART(dw, CONVERT(char(10),@input, 101)), CONVERT(char(10), @input, 101)) AS datetime)else if @groupformat = 'MONTH'set @dtvar = CAST(CONVERT(CHAR(6), @input, 112) + '01' AS datetime)return @dtvarEND
View 9 Replies View RelatedThis is x-posted in:alt.php.sqlcomp.databases.ms-sqlservermicrosoft.public.sqlserver.programmingI have events that occur during the day. I want to be able to search thoseby a form with checkboxes (multiple select).Let's say for instance an event is happening from 3-10pm. When someonesearches for 4-6 (checkbox option) it needs to show up.I don't need code so much as I just need theory. My theory that I coded outand worked, just a missight in theory is as follows. I did a BETWEEN callthat pulled any event that began BETWEEN 4 AND 6 or ended BETWEEN 4 AND 6.As you can see. The event spans that time, but does not start or stopbetween 4 and 6, thus was not pulled. Ooops.So if someone call tell me of another function or perhaps just a better wayto use BETWEEN that would be great. I don't think that code is necessary atthis juncture, so save the 'Please post code' post :) Thanks.
View 12 Replies View RelatedHello,
I am using sp_executesql this to pass parameter to sql string and I am seeing deadlock between sp_prepexec which does UPDATE with another UPDATE done by another process. When it comes to speed and deadlock, would you recomand not using sp_executesql?
Hi,
Can anyone tell me a way to speed up these querys?
//This is selecting a number of records (sent by user) from a table and randomizing those
tempSQL.Text = "select top " + amount.Text + " number from [" + src.Text + "] Where pull='N' order by newID()";
SqlConnection conn2 = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
conn2.Open();
SqlCommand cmd3 = new SqlCommand(tempSQL.Text, conn2);
cmd3.CommandTimeout = 1000;
SqlDataReader dr = cmd3.ExecuteReader();
//Then I open a data reader that uses the records
SqlConnection conn2a = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
conn2a.Open();
while (dr.Read())
{
//the records are then placed 1 by one into a temp table
string fillresultID = "Insert into [" + src.Text + "_Additional_Temp] (number) Values('" + dr["number"] + "')";
SqlCommand cmd4 = new SqlCommand(fillresultID, conn2a);
cmd4.CommandTimeout = 0;
cmd4.ExecuteNonQuery();
//then the original table that held the numbers is marked as used(again one by one)
string update = "Update [" + src.Text + "] set pull='Y' where number='" + dr["number"] + "'";
SqlCommand cmd5 = new SqlCommand(update, conn2a);
cmd5.CommandTimeout = 0;
cmd5.ExecuteNonQuery();
}
dr.Close();
conn2.Close();
conn2a.Close();
Thanks,
Doug
Hi, how can i speed up this query, it seems to be taking a very long time to bring back the reults;
--This stored procedure retrieves access rights for usersCREATE PROCEDURE wc_User_Access_Right_List
ASSELECT dbo.tblRep.Rep_ID, RTRIM(dbo.tblRep.Rep_Forename) + ' ' + RTRIM(dbo.tblRep.Rep_Surname) AS User_Full_Name, dbo.tblAccessRight.Access_Right, dbo.tblAccessRight.Access_Right_IDFROM dbo.tblRep LEFT OUTER JOIN dbo.tblAccessRight ON dbo.tblRep.Access_Right_ID = dbo.tblAccessRight.Access_Right_ID ORDER BY User_Full_Name
--Make sure this has saved, if not return 10 as this is unexpected error
IF @@rowcount = 0 return 10
DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO
I have a select statement that has many 'ands' where one side uses the same thing for ex.
where
t1.column1=r3.other and
t1.column1=e5.new and
t1.column1=k9.old etc...is there any speed gained by putting the value for column one into a variable and using that variable each time instead of t1.columns1?
Thanks,
Eddie
I have indexed my SQL Server tables to gain some speed on calling up tables and queries ( using VB and ADO ). It is still very slow...Is there a move I have to make once my tables are indexed or is there any tricks to improve the speed cause I am getting kinda desparate right now :(
View 1 Replies View RelatedI am in the planning stages of a website and want to design my database to allow for optimum performance in case the site becomes popular. In my database I will have around 5 main categories with each category having around 25 or 30 subcategories and each subcategory having around 100 to 300 items. I plan to use MS-SQL Server7. Would it be best to have one large table or have 5 tables for the main categories or have around 100 tables one for each subcategory? Database usage will be simple (no complex queries) but most pages will hit the database and I need to allow for a potential of 4 or 5 million page views per month. The SQL Server will be a shared one.
Randall
Howdy,
Any one got any tricks on speeding up storing and retrieving image data? I have a typical 2MB image and I am trying to improve speed... it now takes 8 seconds to store the blob.
thanks
Dick Butler
I have migrated a database from ms access to ms ms sql. But I am encountering a problem with the website loading speed. It was running much faster with ms access then it is running with ms sql.
Any inputs or thoughts are appreciated.
Treat it as urgent.
We are currently running Backup Exec v7.0 and are backing up two SQL 6.5 servers on our network. The backups
have begun taking excessive amounts of time considering the limited amounts of data on the servers. Does anyone have
an idea of parameters we could check on the servers or other settings that should be checked in Backup Exec? Also, this
problem seems to have surfaced after we loaded Network Associates VirusScan NT onto our NT Servers and Workstations.
Has anyone had problems with this product on their SQL servers?
Hi
I've recently been given the task to maintain a website that runs off mssql using vb and asp .net. One of the main task is to improve the mssql access time. I am fairly new to mssql, can anyone give me some insight on the procedure to improve access time to mssql. Obviously sql query plays a big part, but what about database config, asp/vb .net commands or design patterns (for large amount of data pulling and join?)
Any suggestions or pointer will be greatly appreciated.
I have a single UPDATE statement that has been running for 13+ hours and I have no idea of when it will complete.
Recipients has 80 million records
UpdatedStagingRecipients has 34 million records.
Why would this possibly take so long? Is there anything that I can do at all?
UPDATE Recipients
SET Recipients.First = UpdatedStagingRecipients.First
, Recipients.Last = UpdatedStagingRecipients.Last
, Recipients.StreetAddress = UpdatedStagingRecipients.StreetAddress
, Recipients.City = UpdatedStagingRecipients.City
, Recipients.State = UpdatedStagingRecipients.State
, Recipients.Postal = UpdatedStagingRecipients.Postal
, Recipients.Country = UpdatedStagingRecipients.Country
, Recipients.DOB = UpdatedStagingRecipients.DOB
, Recipients.Obscene = UpdatedStagingRecipients.Obscene
, Recipients.Gender = UpdatedStagingRecipients.Gender
, Recipients.IPv4 = UpdatedStagingRecipients.IPv4
, Recipients.NameSourceID = UpdatedStagingRecipients.NameSourceID
, Recipients.NameLine = UpdatedStagingRecipients.NameLine
, Recipients.AddressSourceID = UpdatedStagingRecipients.AddressSourceID
, Recipients.AddressLine = UpdatedStagingRecipients.AddressLine
, Recipients.RecordCreationSourceID = UpdatedStagingRecipients.RecordCreationSourceID
FROM Recipients INNER JOIN UpdatedStagingRecipients ON (Recipients.UserName = UpdatedStagingRecipients.UserName AND Recipients.DomainID = UpdatedStagingRecipients.DomainID)
TRUNCATE TABLE UpdatedStagingRecipients
it is working but takes about 3-4 seconds per exec.
CREATE PROCEDURE isp_ap_calc_apt_totals
@p_comp char(2),
@p_vend char(6),
@p_asofdatechar(8)
as
if (@p_asofdate <= '00000000')
begin
set @p_asofdate = '99999999'
end
delete from XAPAPTTOT
where xapt_comp = @p_comp and xapt_vend = @p_vend and xapt_asof_date = @p_asofdate
insert into XAPAPTTOT
select apph_comp, apph_vend, apph_type, apph_id, @p_asofdate,
sum(apph_paymnts),
sum(apph_discts),
sum(apph_adjts),
count(apph_paymnts),
sum(apph_paymnts)+ sum(apph_discts) + sum(apph_adjts) +
b.apt_gross,
0,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0'))
from APPHISTF.a join APTRANF.b on b.apt_comp = a.apph_comp and b.apt_vend = a.apph_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id
where ((a.apph_comp = @p_comp) and (a.apph_vend = @p_vend) and (a.apph_unpost_dt = 0)
and (str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @p_asofdate))
or ((a.apph_unpost_dt > 0 and a.apph_unpost_dt <= @p_asofdate and b.apt_unposted_fg = 1 and b.apt_comp = @p_comp and b.apt_vend = @p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
or (((str(a.yy,4) + replace(str(a.mm,2),' ','0') + replace(str(a.dd,2),' ','0') <= @p_asofdate) and a.apph_unpost_dt > @p_asofdate and b.apt_comp = @p_comp and b.apt_vend = @p_vend and b.apt_type = a.apph_type and b.apt_id = a.apph_id))
group by apph_comp, apph_vend, apph_type, apph_id
update XAPAPTTOT
set xapt_last_payck =
(select max(apph_payck) from APPHISTF
where apph_comp = xapt_comp and apph_vend = xapt_vend and apph_type = xapt_type
and apph_id = xapt_id
and str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') = xapt_last_paydt )
where xapt_comp = @p_comp and xapt_vend = @p_vend and xapt_asof_date = @p_asofdate
GO
I'm not sure if this is the right forum, but I have a general question about running/storing databases. I have been running a process with 60+ million records in one table and another 16 million in another table and it is taking forever to get everything imported in and run the appropriate queries. I've been doing this all on a desktop and I am anxious to learn of a more efficient, faster method of processing this amount of data.
What solution should I pursue if I am doing this work a few times a year so that it doesn't take three full days of processing to reach an answer with the data?
Thanks.
Has anyone started using encryption yet? Is it noticably slower than not using it? For the record Im not refering too column encryption, but "network" (not sure what else to call it) encryption when it's encrypted between SQL Server and the client.
View 1 Replies View RelatedHi Everyone -
We were discussing select speeds the other day,
the question comes in at...
what is faster, a standard query with specifies the search critera
i.e. select * from xyz where a='A' or a='B'
or is the IN a better way to go....
select * from xyz where a in ('A', 'B')
we ran the tests in performance analyzer,
but they were the same results....
maybe i'm smoking the stuff - but i thought the
useage of the IN causes a full table scan for each
of the items in the in clause
please advise
take care
tony
This may be more appropriate on another forum, but wanted to see if anyone could give me any ideas.
We have SQL 7 on NT4. We are restricted from upgrading for a couple of particular reasons, but things nevertheless run fairly stable.
We have roughly 100 - 140 simultaneous connections.
Some users have very slow access times where others have a fast connection time. This is not restricted to DB access, but also for file transferring.
Just like you can test your DSL/Cable speeds, are there any similar tests that can be ran on client machines to find their access speeds to the SQL database server?
I really appreciate your suggestions!
I need to speed this process up.
Insert into dbo.temp_table
(record_number, etc....)
select record_number, etc....
from
dbo.incoming_temp
set Indentity _insert temp_table on
It's about 10 million records and usally runs 2-3 hours. Any ideas???????????
Hello,
I am working with some third party software and trying to rebuild my own queries for my own reports but using the business logic that is behind their queries so that I can be sure the numbers still match up and are useable. The reports in the third party software seem to be built on the fly as I am finding when I run sql profiler because there are no stored procedures. I have a query which returns a basic result set but it takes 52 seconds to get it back and I feel like that is way to long for such a simple result set of returning a count by location. The query creates a few temporary tables and combines and updates some data. I want to update this query to make it faster but am not sure of the best approach to take. The temporary tables create alot of columns that I am not even going to need in the final result set or the update statements so I thought by removing some it would help. I am going to paste in the original query and if anyone has any ideas to make this more up to date with sql 2005 standards and just faster please feel free to let me know. Thanks! :)
CREATE TABLE #FI28EF3WE3DN
(practice_id char(4) null, location_id uniqueidentifier null, rendering_id uniqueidentifier null, service_item_id char(12) null, service_item_lib_id uniqueidentifier null, service_item_desc varchar(80) null, cpt4_code_id char(12) null, charge_amt numeric(19,2) null, quantity dec(19,2) null, begin_date_of_service char(8) null, closing_date char(8) null, create_timestamp datetime null, rvu1 float null, rvu1_total float null, rvu2 float null, rvu2_total float null, rvu3 float null, rvu3_total float null, rvu4 float null, rvu4_total float null, end_date_of_service char(8) null, midlevel_id uniqueidentifier null, unit_price numeric(19,2) null, override_ind char(1) null, modifier_1 char(2) null, modifier_2 char(2) null, modifier_3 char(2) null, modifier_4 char(2) null, link_id uniqueidentifier null, source_id uniqueidentifier null, enc_nbr numeric null, source_type char(1) null, name varchar(150) null, person_id uniqueidentifier null, date_of_birth char(8) null, patient_age char(8) null, address_line_1 varchar(55) null, address_line_2 varchar(55) null, city varchar(35) null, state varchar(3) null, zip char(9) null, country_id uniqueidentifier null, county_id uniqueidentifier null, county_desc varchar(100) null, sex char(1) null, sex_desc varchar(30) null, batch_info varchar(40) null, override_closing_date char(8) null, ud_demo1_id uniqueidentifier null, ud_demo2_id uniqueidentifier null, ud_demo3_id uniqueidentifier null, ud_demo4_id uniqueidentifier null, ud_demo5_id uniqueidentifier null, ud_demo6_id uniqueidentifier null, ud_demo7_id uniqueidentifier null, ud_demo8_id uniqueidentifier null, ud_demo9_id uniqueidentifier null, ud_demo10_id uniqueidentifier null, icd9cm_code_id varchar(10) null, icd9cm_code_id_2 varchar(10) null, icd9cm_code_id_3 varchar(10) null, icd9cm_code_id_4 varchar(10) null, icd9cm_code_desc varchar(255) null, icd9cm_code_desc_2 varchar(255) null, icd9cm_code_desc_3 varchar(255) null, icd9cm_code_desc_4 varchar(255) null, quadrant char(2) null, tooth char(2) null, surface_description varchar(200) null, form char(4) null, fin_class_id uniqueidentifier, person_payer_id uniqueidentifier, department_id uniqueidentifier, outsource_ind char(1) null, outsource_date char(8) null, outsource_agency_id uniqueidentifier, outsource_agency_desc varchar(40) null, fqhc_enc_ind char(1) null, fin_class varchar(100) null, payer_name varchar(40) null, person_nbr char(12) null, pat_prov_name_1 varchar(75), pat_prov_name_2 varchar(75), pat_prov_name_3 varchar(75), pat_prov_name_4 varchar(75), pat_prov_name_5 varchar(75), pat_prov_name_6 varchar(75), pat_prov_name_7 varchar(75), pat_prov_name_8 varchar(75), pat_prov_name_9 varchar(75), pat_prov_name_10 varchar(75), pat_prov_name_11 varchar(75), pat_prov_name_12 varchar(75))
INSERT into #FI28EF3WE3DN
(practice_id, source_id, source_type, location_id, rendering_id, service_item_id, service_item_lib_id, cpt4_code_id, charge_amt, quantity, begin_date_of_service, closing_date, create_timestamp, rvu1_total, rvu2_total, rvu3_total, rvu4_total, end_date_of_service, modifier_1, modifier_2, modifier_3, modifier_4, midlevel_id, unit_price, override_ind, link_id, person_id, override_closing_date, batch_info, icd9cm_code_id, icd9cm_code_id_2, icd9cm_code_id_3, icd9cm_code_id_4, icd9cm_code_desc, icd9cm_code_desc_2, icd9cm_code_desc_3, icd9cm_code_desc_4, quadrant, tooth, surface_description, form, fin_class_id, person_payer_id, outsource_ind, outsource_date, outsource_agency_id, fqhc_enc_ind)
SELECT c.practice_id, c.source_id, c.source_type, c.location_id, c.rendering_id, c.service_item_id, c.service_item_lib_id, c.cpt4_code_id, amt, quantity, begin_date_of_service, closing_date, c.create_timestamp, 0.0000, 0.0000, 0.0000, 0.0000, end_date_of_service, c.modifier_1, c.modifier_2, modifier_3, modifier_4, midlevel_id, unit_price, override_ind, link_id, c.person_id, override_closing_date, batch_info, c.icd9cm_code_id, c.icd9cm_code_id_2, c.icd9cm_code_id_3, c.icd9cm_code_id_4, icd9cm_code_desc, icd9cm_code_desc_2, icd9cm_code_desc_3, icd9cm_code_desc_4, quadrant, tooth, surface_description, c.form, p.financial_class, pe.cob1_person_payer_id, outsource_ind, outsource_date, outsource_agency_id, pe.fqhc_enc_ind
FROM charges c, patient_encounter pe, payer_mstr p
WHERE source_type = 'V' and c.practice_id = pe.practice_id and c.source_id = pe.enc_id and pe.cob1_payer_id = p.payer_id and begin_date_of_service >= '20080401' and begin_date_of_service < '20080501' and (c.link_id is null) AND c.practice_id = '0001'
INSERT into #FI28EF3WE3DN
(practice_id, source_id, source_type, location_id, rendering_id, service_item_id, service_item_lib_id, cpt4_code_id, charge_amt, quantity, begin_date_of_service, closing_date, create_timestamp, rvu1_total, rvu2_total, rvu3_total, rvu4_total, end_date_of_service, modifier_1, modifier_2, modifier_3, modifier_4, midlevel_id, unit_price, override_ind, link_id, person_id, override_closing_date, batch_info, icd9cm_code_id, icd9cm_code_id_2, icd9cm_code_id_3, icd9cm_code_id_4, icd9cm_code_desc, icd9cm_code_desc_2, icd9cm_code_desc_3, icd9cm_code_desc_4, quadrant, tooth, surface_description, form, outsource_ind, outsource_date, outsource_agency_id, fqhc_enc_ind) SELECT c.practice_id, c.source_id, c.source_type, c.location_id, c.rendering_id, c.service_item_id, c.service_item_lib_id, c.cpt4_code_id, amt, quantity, begin_date_of_service, closing_date, c.create_timestamp, 0.0000, 0.0000, 0.0000, 0.0000, end_date_of_service, c.modifier_1, c.modifier_2, modifier_3, modifier_4, midlevel_id, unit_price, override_ind, link_id, c.person_id, override_closing_date, batch_info, c.icd9cm_code_id, c.icd9cm_code_id_2, c.icd9cm_code_id_3, c.icd9cm_code_id_4, icd9cm_code_desc, icd9cm_code_desc_2, icd9cm_code_desc_3, icd9cm_code_desc_4, quadrant, tooth, surface_description, c.form, outsource_ind, outsource_date, outsource_agency_id, pe.fqhc_enc_ind FROM charges c, patient_encounter pe
WHERE source_type = 'V' and c.practice_id = pe.practice_id and c.source_id = pe.enc_id and pe.cob1_person_payer_id is null and begin_date_of_service >= '20080401' and begin_date_of_service < '20080501' and (c.link_id is null) AND c.practice_id = '0001'
INSERT into #FI28EF3WE3DN
(practice_id, source_id, source_type, location_id, rendering_id, service_item_id, service_item_lib_id, cpt4_code_id, charge_amt, quantity, begin_date_of_service, closing_date, create_timestamp, rvu1_total, rvu2_total, rvu3_total, rvu4_total, end_date_of_service, modifier_1, modifier_2, modifier_3, modifier_4, midlevel_id, unit_price, override_ind, link_id, person_id, override_closing_date, batch_info, icd9cm_code_id, icd9cm_code_id_2, icd9cm_code_id_3, icd9cm_code_id_4, icd9cm_code_desc, icd9cm_code_desc_2, icd9cm_code_desc_3, icd9cm_code_desc_4, quadrant, tooth, surface_description, form, outsource_ind)
SELECT c.practice_id, c.source_id, c.source_type, c.location_id, c.rendering_id, c.service_item_id, c.service_item_lib_id, c.cpt4_code_id, amt, quantity, begin_date_of_service, closing_date, c.create_timestamp, 0.0000, 0.0000, 0.0000, 0.0000, end_date_of_service, c.modifier_1, c.modifier_2, modifier_3, modifier_4, midlevel_id, unit_price, override_ind, link_id, c.person_id, override_closing_date, batch_info, c.icd9cm_code_id, c.icd9cm_code_id_2, c.icd9cm_code_id_3, c.icd9cm_code_id_4, icd9cm_code_desc, icd9cm_code_desc_2, icd9cm_code_desc_3, icd9cm_code_desc_4, quadrant, tooth, surface_description, c.form, c.outsource_ind FROM charges c
WHERE source_type = 'I' and begin_date_of_service >= '20080401' and begin_date_of_service < '20080501' and (c.link_id is null) AND c.practice_id = '0001'
UPDATE #FI28EF3WE3DN
SET name = isnull(per.last_name,'') + ', ' + isnull(per.first_name,'') + ' ' + isnull(per.middle_name,''), date_of_birth = per.date_of_birth, patient_age = per.date_of_birth, city = per.city, zip = per.zip, country_id = per.country_id, sex = per.sex, address_line_1 = per.address_line_1, address_line_2 = per.address_line_2, state = per.state, county_id = per.county_id, person_nbr = per.person_nbr
FROM person per
WHERE #FI28EF3WE3DN.person_id = per.person_id
UPDATE #FI28EF3WE3DN
SET name = emp.name, city = emp.city, zip = emp.zip, address_line_1 = emp.address_line_1, address_line_2 = emp.address_line_2, state = emp.state, county_id = emp.county_id
FROM invoices inv, accounts ac, employer_mstr emp
WHERE #FI28EF3WE3DN.source_type = 'I' and #FI28EF3WE3DN.practice_id = inv.practice_id and #FI28EF3WE3DN.source_id = inv.invoice_id and inv.practice_id = ac.practice_id and inv.acct_id = ac.acct_id and ac.guar_type = 'E' and ac.guar_id = emp.employer_id
CREATE TABLE #GP28EF3WE3DQ
(practice_id char(4) null, location_id uniqueidentifier null, practice_name varchar(40) null, location_desc varchar(40) null, rendering_id uniqueidentifier null, attend_first_name varchar(25) null, attend_middle_name varchar(25) null, attend_last_name varchar(25) null, service_item_id char(12) null, service_item_desc varchar(80) null, cpt4_code_id char(12) null, cpt4_desc varchar(50) null, charge_amt numeric(19,2) null, quantity dec(19,2) null, begin_date_of_service char(8) null, closing_date char(8) null, create_timestamp datetime null, rvu1 float null, rvu1_total float null, rvu2 float null, rvu2_total float null, rvu3 float null, rvu3_total float null, rvu4 float null, rvu4_total float null, end_date_of_service char(8) null, midlevel_id uniqueidentifier null, unit_price numeric(19,2) null, override_ind char(1) null, modifier_1 char(2) null, modifier_2 char(2) null, modifier_3 char(2) null, modifier_4 char(2) null, link_id uniqueidentifier null, mid_last_name varchar(25) NULL, mid_first_name varchar(25) NULL, mid_middle_name varchar(25) NULL, link_ind char(1) null, name varchar(150) null, date_of_birth char(8) null, patient_age char(8) null, address_line_1 varchar(55) null, address_line_2 varchar(55) null, city varchar(35) null, state varchar(3) null, zip char(9) null, country_id uniqueidentifier null, county_id uniqueidentifier null, county_desc varchar(100) null, sex char(1) null, sex_desc varchar(30) null, batch_info varchar(40) null, override_closing_date char(8) null, location_subgrouping1_id uniqueidentifier null, location_subgrouping1_desc varchar(100) null, location_subgrouping2_id uniqueidentifier null, location_subgrouping2_desc varchar(100) null, provider_subgrp1_id uniqueidentifier null, provider_subgrp1_desc varchar(100) null, provider_subgrp2_id uniqueidentifier null, provider_subgrp2_desc varchar(100) null, ud_demo1_id uniqueidentifier null, ud_demo1 char(100) null, ud_demo2_id uniqueidentifier null, ud_demo2 char(100) null, ud_demo3_id uniqueidentifier null, ud_demo3 char(100) null, ud_demo4_id uniqueidentifier null, ud_demo4 char(100) null, ud_demo5_id uniqueidentifier null, ud_demo5 char(100) null, ud_demo6_id uniqueidentifier null, ud_demo6 char(100) null, ud_demo7_id uniqueidentifier null, ud_demo7 char(100) null, ud_demo8_id uniqueidentifier null, ud_demo8 char(100) null, ud_demo9_id uniqueidentifier null, ud_demo9 char(100) null, ud_demo10_id uniqueidentifier null, ud_demo10 char(100) null, icd9cm_code_id varchar(10) null, icd9cm_code_id_2 varchar(10) null, icd9cm_code_id_3 varchar(10) null, icd9cm_code_id_4 varchar(10) null, icd9cm_code_desc varchar(100) null, icd9cm_code_desc_2 varchar(100) null, icd9cm_code_desc_3 varchar(100) null, icd9cm_code_desc_4 varchar(100) null, quadrant char(2) null, tooth char(2) null, form char(4) null, quad_desc varchar(200) null, tooth_desc varchar(200) null, surface_description varchar(200) null, form_desc varchar(200) null, department_id uniqueidentifier, fin_class varchar(100) null, payer_name varchar(40) null, department varchar(100) null, outsource_ind char(1) null, outsource_date char(8) null, outsource_agency_desc varchar(40) null, fqhc_enc_ind char(1) null, person_nbr char(12) null, pat_prov_name_1 varchar(75), pat_prov_name_2 varchar(75), pat_prov_name_3 varchar(75), pat_prov_name_4 varchar(75), pat_prov_name_5 varchar(75), pat_prov_name_6 varchar(75), pat_prov_name_7 varchar(75), pat_prov_name_8 varchar(75), pat_prov_name_9 varchar(75), pat_prov_name_10 varchar(75), pat_prov_name_11 varchar(75), pat_prov_name_12 varchar(75))
INSERT into #GP28EF3WE3DQ
(practice_id, location_id, rendering_id, service_item_id, service_item_desc, cpt4_code_id, charge_amt, quantity, begin_date_of_service, closing_date, create_timestamp, rvu1, rvu1_total, rvu2, rvu2_total, rvu3, rvu3_total, rvu4, rvu4_total, end_date_of_service, modifier_1, modifier_2, modifier_3, modifier_4, midlevel_id, unit_price, override_ind, link_id, name, date_of_birth, patient_age, address_line_1, address_line_2, city, state, zip, country_id, county_id, sex, override_closing_date, batch_info, ud_demo1_id, ud_demo2_id, ud_demo3_id, ud_demo4_id, ud_demo5_id, ud_demo6_id, ud_demo7_id, ud_demo8_id, ud_demo9_id, ud_demo10_id, icd9cm_code_id, icd9cm_code_id_2, icd9cm_code_id_3, icd9cm_code_id_4, icd9cm_code_desc, icd9cm_code_desc_2, icd9cm_code_desc_3, icd9cm_code_desc_4, quadrant, tooth, surface_description, form, department_id, outsource_ind, outsource_date, outsource_agency_desc, fqhc_enc_ind, fin_class, payer_name, person_nbr, pat_prov_name_1, pat_prov_name_2, pat_prov_name_3, pat_prov_name_4, pat_prov_name_5, pat_prov_name_6, pat_prov_name_7, pat_prov_name_8, pat_prov_name_9, pat_prov_name_10, pat_prov_name_11, pat_prov_name_12)
SELECT practice_id, location_id, rendering_id, null , null , null , sum(isnull(charge_amt,0)), sum(isnull(quantity,0)), begin_date_of_service, null , null , null , sum(isnull(rvu1_total,0)), null , sum(isnull(rvu2_total,0)), null , sum(isnull(rvu3_total,0)), null , sum(isnull(rvu4_total,0)), null , null , null , null , null , null , null , null , null , name, date_of_birth, null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null
FROM #FI28EF3WE3DN group by practice_id, location_id, rendering_id, begin_date_of_service, name, date_of_birth
UPDATE #GP28EF3WE3DQ
SET attend_first_name = pm.first_name, attend_middle_name = pm.middle_name, attend_last_name = pm.last_name, provider_subgrp1_id = pm.provider_subgrouping1_id, provider_subgrp2_id = pm.provider_subgrouping2_id FROM provider_mstr pm
WHERE #GP28EF3WE3DQ.rendering_id = pm.provider_id
UPDATE #GP28EF3WE3DQ
SET practice_name = p.practice_name FROM practice p
WHERE #GP28EF3WE3DQ.practice_id = p.practice_id
UPDATE #GP28EF3WE3DQ
SET location_desc = l.location_name, location_subgrouping1_id = l.location_subgrouping1_id, location_subgrouping2_id = l.location_subgrouping2_id
FROM location_mstr l
WHERE #GP28EF3WE3DQ.location_id = l.location_id
UPDATE #GP28EF3WE3DQ SET link_ind = 'Y'
WHERE link_id is not null
UPDATE #GP28EF3WE3DQ
SET #GP28EF3WE3DQ.department = ml.mstr_list_item_desc
FROM mstr_lists ml
WHERE #GP28EF3WE3DQ.department_id = ml.mstr_list_item_id and mstr_list_type = 'department'
UPDATE #GP28EF3WE3DQ
SET county_desc = ml.mstr_list_item_desc
FROM mstr_lists ml
WHERE #GP28EF3WE3DQ.county_id = ml.mstr_list_item_id and ml.mstr_list_type = 'county'
select location_desc,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null, count(*) from #GP28EF3WE3DQ GROUP BY location_desc Order By location_desc
Hello all,
I am very astonished about the following (recplus has 4.2 Million records).
if I do this :
select sum (quantityInKg) from recplus where element_ID in (160,
161,
162,
164,
165,
166,
1756,
21707,
22052,
22063)
it takes about 2 seconds.
If I do this
select sum (quantityInKg) from recplus where element_ID in (select element_2_id from element_element where element_id = '159')
it takes 1 second. (select element_2_id from element_element where element_id = '159' gives the same list as in the previous query)
Now, why is there a speed diffrence, or better, why in this way? I would have expected it to be the othere way around !
Thanks for your feedbacks.
Regards,
Fabianus
my favorit hoster is ASPnix : www.aspnix.com !
Hello all
I am running a query where the same table is join 4 times. Due to this number of records goes to millions. It take more than 5 min for the query to run. I am trying to optimize the query.
Anybody please help me in the regards
Thank You
Hello,
I have a question regarding the SQL Lite Speed which primarily used for the backup setups.
Is there any document that I can get OR through the help threads where I can learn to use the SQL Lite Speed.
Thanks.
Vasanth David P.
Ok,This script is something I wrote for bringing up a report in reportingservices and it is really slow...Is their any problems with it or istheir better syntax to speed it up and still provide the same reportresults?:SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,t6.amount_type,SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amountWHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amountWHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amountWHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amountWHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amountWHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amountWHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amountWHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amountWHEN t2.amountTypeId = 15 THEN - t2.amount END) AS SpentFROM tblTravelDetail t1 INNER JOINtblTravelDetailAmount t2 ON t1.TravelDetailId =t2.TravelDetailId INNER JOINtblTravelDetail t3 ON t2.TravelDetailId =t3.TravelDetailId INNER JOINtblTravelDetailMember t4 ON t3.TravelDetailId =t4.TravelDetailId INNER JOINtblTravelEvent t5 ON t1.TravelEventId =t5.TravelEventId INNER JOINamount_type t6 ON t2.amountTypeId =t6.amount_type_id INNER JOINperiod t8 ON t1.PeriodID = t8.period_idWHERE (t1.MemberId = @MemberId) AND (t2.amount <> 0)GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,t6.amount_typeAny help is appreciated.Thanks,Trint
View 4 Replies View Relatedi have high cpu problem. i don't know why many .net sqlclinet dataprovider and IIS hold so much cpu resource. by the way, memerycondition is normal.when i restart sqlserver, from windows task manager, i see the cpu timeof sqlserver.exe process is low, but it grows gradually, and in twodays, it can grow to as high as 2:xx:xx.any one can tell me why .net sqlclinet data provider and IIS hold somuch cpu resource? and why cpu time of sqlserver.exe grows gradually?thanks a lot.below is the part of a trace report:TextDataApplicationNameDurationStartTimeReadsWritesCPUNULL.Net SqlClient Data Provider189123357:46.529168411040076NULL.Net SqlClient Data Provider277798310:19.27923583730414NULL.Net SqlClient Data Provider189504657:42.722503411526282NULL.Net SqlClient Data Provider189957657:38.2161560740425570NULLInternet Information Services43009602:52.77042962721518NULL.Net SqlClient Data Provider277362310:19.16577581019828NULL.Net SqlClient Data Provider68673308:21.03144731014904NULLInternet Information Services33395630:11.666142618512188NULLInternet Information Services60609310:07.49384117111124NULL.Net SqlClient Data Provider37281323:04.9111621010686NULL.Net SqlClient Data Provider170658028:33.72410832077626NULLInternet Information Services64606024:35.3238936207390NULL.Net SqlClient Data Provider109062338:32.911946207109NULL.Net SqlClient Data Provider89465642:20.85394306778NULL.Net SqlClient Data Provider78986006:40.766466706483NULL.Net SqlClient Data Provider68856331:44.82884206420NULL.Net SqlClient Data Provider79023344:29.73802006077NULL.Net SqlClient Data Provider56975000:21.587254356049NULL.Net SqlClient Data Provider110423338:12.911020565453NULL.Net SqlClient Data Provider56978000:21.5111749415217NULL.Net SqlClient Data Provider149207631:42.65649825122NULL.Net SqlClient Data Provider142872027:40.27825734877NULL.Net SqlClient Data Provider173951628:27.111265694653NULL.Net SqlClient Data Provider169073328:26.59107804640NULL.Net SqlClient Data Provider40656306:52.74912304391NULL.Net SqlClient Data Provider56973601:17.73792804344NULL.Net SqlClient Data Provider87886028:25.46157204280NULL.Net SqlClient Data Provider78981306:40.7160337554279NULL.Net SqlClient Data Provider28615610:42.01521704172NULL.Net SqlClient Data Provider180126328:07.98573904138NULL.Net SqlClient Data Provider26623352:11.54391104048NULL.Net SqlClient Data Provider42412613:13.33311404046NULL.Net SqlClient Data Provider45828349:48.71569404014NULL.Net SqlClient Data Provider59893648:20.44212603983NULLInternet Information Services38987657:32.910983643923NULL.Net SqlClient Data Provider40825051:56.11565203922NULL.Net SqlClient Data Provider44153050:05.41727103906NULL.Net SqlClient Data Provider49392303:29.66312503890NULL.Net SqlClient Data Provider35761025:20.51493003797NULL.Net SqlClient Data Provider35456343:38.81570403782NULL.Net SqlClient Data Provider37890649:44.61523103689NULL.Net SqlClient Data Provider36323643:30.21596403673NULL.Net SqlClient Data Provider43392348:08.21566003672NULL.Net SqlClient Data Provider22059321:53.91434903645NULL.Net SqlClient Data Provider184018628:04.68771003637NULL.Net SqlClient Data Provider40776351:38.71646203595NULL.Net SqlClient Data Provider40970350:47.01393003389NULL.Net SqlClient Data Provider65773645:36.83863503376NULL.Net SqlClient Data Provider44501348:17.71387003342NULL.Net SqlClient Data Provider40331351:24.91290503266NULLInternet Information Services60418628:05.5146475413035NULLInternet Information Services60220337:13.79609252955NULL.Net SqlClient Data Provider34518623:32.612010402955NULL.Net SqlClient Data Provider30251314:52.01217902937NULL.Net SqlClient Data Provider37945357:47.331948102891NULL.Net SqlClient Data Provider35692347:15.61096402564NULL.Net SqlClient Data Provider37964019:33.51062602453NULL.Net SqlClient Data Provider161006329:47.711812422391NULL.Net SqlClient Data Provider75999648:30.68796232376NULL.Net SqlClient Data Provider76001348:30.65651302187NULL.Net SqlClient Data Provider158300030:14.79316502157NULLInternet Information Services55791032:46.851272111967NULLInternet Information Services25312653:29.67935541893NULL.Net SqlClient Data Provider95000027:14.33036801875NULL.Net SqlClient Data Provider56968607:22.5112528421842NULLInternet Information Services21875054:04.03855301811NULL.Net SqlClient Data Provider126831335:28.4162994581782NULL.Net SqlClient Data Provider141667332:47.97308401674NULLInternet Information Services55525047:41.07781181639NULL.Net SqlClient Data Provider88247028:21.84272901638NULLInternet Information Services25129653:31.553638181626NULL.Net SqlClient Data Provider88981328:14.44857501625NULLInternet Information Services19851600:16.159345501580NULL.Net SqlClient Data Provider56970601:17.77347301576NULLInternet Information Services52651647:51.24524581565NULL.Net SqlClient Data Provider37881326:26.96875301467NULL.Net SqlClient Data Provider81989043:15.75487301312NULLInternet Information Services37651632:48.275602141297NULL.Net SqlClient Data Provider30257614:51.91455351801266NULL.Net SqlClient Data Provider94970357:49.66191501238NULL.Net SqlClient Data Provider34781323:29.93622901203NULL.Net SqlClient Data Provider76679630:17.53368101170NULL.Net SqlClient Data Provider107081039:00.44675901109NULL.Net SqlClient Data Provider49532648:22.548276101095NULL.Net SqlClient Data Provider50011048:49.252299511078NULL.Net SqlClient Data Provider37954657:49.7448401062NULL.Net SqlClient Data Provider88336028:20.94006501049NULL.Net SqlClient Data Provider24511033:33.0489701032NULL.Net SqlClient Data Provider88495328:19.33754801031NULL.Net SqlClient Data Provider60478303:26.6451490968NULL.Net SqlClient Data Provider49395303:29.5640980955NULL.Net SqlClient Data Provider54786019:17.0377930938NULLInternet Information Services29589006:38.7446491907NULL.Net SqlClient Data Provider33442300:18.767960891NULL.Net SqlClient Data Provider86037628:43.9126870858NULLInternet Information Services40493639:47.8471172843NULL.Net SqlClient Data Provider68973331:53.8315881796NULL.Net SqlClient Data Provider50568649:43.33473121796NULL.Net SqlClient Data Provider50345348:13.1276641764NULL.Net SqlClient Data Provider72609348:20.5210820702NULL.Net SqlClient Data Provider24520333:32.9381320688NULL.Net SqlClient Data Provider28625010:41.9285548686NULL.Net SqlClient Data Provider173781330:13.5169100659NULL.Net SqlClient Data Provider34089023:36.953030625NULL.Net SqlClient Data Provider34144023:36.327820625NULL.Net SqlClient Data Provider41397050:40.9287224610NULL.Net SqlClient Data Provider34073323:37.0213300608NULL.Net SqlClient Data Provider53148348:02.7270060595NULL.Net SqlClient Data Provider35764025:20.5402862594NULL.Net SqlClient Data Provider56892033:54.6284070593declare @P1 int set @P1=1033 declare @P2 int set @P2=20644 execp_splitpage @sql = 'SelectUserName,Status,Qymc,Qylb,areaCode1,SiteHits,Zycp, CONVERT(varchar(300), Qyjj) AS Qyjj,QyjjStatus,compPhone,compFax From UserInfoWhere isValid=1 and Right(Status,1).Net SqlClient DataProvider481308:18.211296955578NULL.Net SqlClient Data Provider49670348:13.6268720563NULL.Net SqlClient Data Provider49137349:58.5275230562NULL.Net SqlClient Data Provider37967019:33.5404290562NULLInternet Information Services39212632:22.61511612543NULL.Net SqlClient Data Provider51453048:13.0228450532NULL.Net SqlClient Data Provider65514048:42.740750531declare @P1 int set @P1=5587 declare @P2 int set @P2=55867 execp_splitpage @sql = 'SelectInfo.picPath,Info.Info_ID,Info.postUser,Info.infoT ype,Info.infoLevel,Info.infoDirect,Info.showname,I nfo.postDateTime,Info.areaCode,CONVERT(varchar(300), Info.conten.Net SqlClient DataProvider94014:55.0139021180516exec p_splitpage 'SelectID,UserName,PassWord,Qymc,RegisterTime,name,Phone From UserInfo WhereisValid = 1 and UserName like ''%nick%'' andSUBSTRING(Status,2,3)=''019'' Order By ID DESC',0,30InternetInformation Services131335:39.814030516NULL.Net SqlClient Data Provider24104607:30.7216430514NULL.Net SqlClient Data Provider56918635:46.737300499NULL.Net SqlClient Data Provider36893650:51.7199720486NULL.Net SqlClient Data Provider102501628:10.9231642485NULL.Net SqlClient Data Provider54798619:16.9340860485NULL.Net SqlClient Data Provider42397013:13.4397300483NULL.Net SqlClient Data Provider37964012:11.1330690470NULLInternet Information Services18441037:30.3261545468NULL.Net SqlClient Data Provider43448606:16.9191610468NULL.Net SqlClient Data Provider36296652:15.4190060436NULLInternet Information Services11064001:43.984251422NULL.Net SqlClient Data Provider37975057:40.8210330419NULL.Net SqlClient Data Provider5279655:44.9159260406NULL.Net SqlClient Data Provider33442300:18.7325502392NULLInternet Information Services11436047:40.4148070390NULL.Net SqlClient Data Provider39812636:26.138510343NULL.Net SqlClient Data Provider155228331:21.2164340329NULL.Net SqlClient Data Provider4187655:55.8164293328NULL.Net SqlClient Data Provider36928023:08.5144192328Select Count(ID) From UserInfo Where isValid=1 and Right(Status,1)<>'0'and hy = '0019'.Net SqlClient Data Provider98308:31.9232480298SELECT TOP 5 L.title, L.siteUrl FROM Links L INNER JOIN LinksCategory CON C.PKID = L.CategoryID INNER JOIN LinksType T ON T.typeID = C.typeIDWHERE (T.typeID = 7).Net SqlClient DataProvider3057654:41.622720282
View 1 Replies View Related