April 19, 2002 at 8:57 am
I'm developing an application for a hospital that needs to fetch a list of people from six different SQL databases ... fields like last name, first name, ssn, dob are common to all of them.
The source tables have names like patients, doctors, staff, insured_members, uninsured_members ... each in a separate SQL Server database.
I have working stored procedures that combine query results from all the sources, however, performance would improve if there were a way to "multi-thread" ... to issue the queries simultaneously.
Anybody know if this is possible and how to multi-thread SQL Server??
April 19, 2002 at 10:27 am
I beleive SQL Server manages it's own thread processing, even to a smaller level called fibers.
-JG
-JG
April 19, 2002 at 10:50 am
If you have a multi CPU box, then the server will "parallelize" the queries, which will run multiple threads or fibers (not the same). On a single proc box, there will not be much benfits, but the server will still send separate threads out for I/O to different databases, so there may be benefits if you have good physical separation of the data drives.
Steve Jones
April 19, 2002 at 11:49 am
The only problem thou with parallelized queries is they have to meet a threshold on the server before this occurrs (this is definable but default is usually best). Also to a great extent multiple threads are being processed anway as multiple queires come into the server. The best you can do with performance is make sure indexes are up to par and you keep the databases maintained, beyond that you are at the will of SQL.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 19, 2002 at 12:44 pm
Just to make clear, the stored procedure I'm alluding to has six sections. Each uses the search criteria to add rows to a temporary table. When all six sources have been added to the temp table, the results are returned.
It would be so nice if there was a way to ask SQL Server to search them all at the same time.
April 26, 2002 at 10:44 am
sdining,
One other thing you can do is on the configuration of the database. SQL Server can do mulitprocessing if the data is located accross mulitple filegroups.
If the computer has multiple processors, SQL Server can perform parallel scans of the data. Multiple parallel scans can be executed for a single table if the table’s filegroup contains multiple files. Whenever a table is accessed sequentially, a separate thread is created to read each file in parallel. For example, a full scan of a table created on a filegroup comprising of four files will use four separate threads to read the data in parallel. Therefore, creating more files per filegroup can help increase performance because a separate thread is used to scan each file in parallel. Similarly, when a query joins tables on different filegroups, each table can be read in parallel, thereby improving query performance.
-JG
-JG
May 1, 2002 at 1:25 am
Some thoughts:
Can't you use distributed Partitioned vievs I guess if fits to ur needs, this is best.
Regarding tables if you are reading lot of data then it's good to place these tables on different filegroups and these filegroups should have multiple files on different disks.
This will use optimum level of Disk I/O.
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
May 1, 2002 at 5:08 am
quote:
Just to make clear, the stored procedure I'm alluding to has six sections. Each uses the search criteria to add rows to a temporary table. When all six sources have been added to the temp table, the results are returned.It would be so nice if there was a way to ask SQL Server to search them all at the same time.
Why specifically are you using a temp table? If the items are related in some way then the temp table is a bad point in you code and should be avoided? Can you post the code or an edited version (table names can be altered and columns) so that we can look and see if there is any suggestions to help you? Individual queries are executed in order when applied the way you alued to but a single query even if a union can process much faster in most cases.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 1, 2002 at 10:48 am
Here is the source code so far. Not all the database sources are programmed yet.
You'll notice the search criteria in the "where" sections are slightly different
(as each source has different fields) and use "case statements".
I guess I could have used "union", however, this would offer SQL Server no way to issues the various database searches simultaneously. My answer to the question is that the only way to get all searches going at the same time is to multi-thread from my Visual Basic program. In other words, to issue a SQL command to each database from a different thread in my program. However, that makes it harder to combine/sort the results. I was hoping for something easier ... some magic in SQL Server.
CREATE PROCEDURE [dbo].[ps_PeopleOrgsSearchMaster]
/*
This stored procedure uses the optional parameters listed below to locate people in the various databases.
*/
@Keane_YorN as tinyint = 0,
@RimsMems_YorN as tinyint = 0,
@RimsProv_YorN as tinyint = 0,
@PMIS_YorN as tinyint = 0,
@Strangers_YorN as tinyint = 0,
@DeptStaff_YorN as tinyint = 0,
@BAC_YorN as tinyint = 0,
@LastName_YorN as tinyint = 0,
@FirstName_YorN as tinyint = 0,
@MRN_YorN as tinyint = 0,
@SSN_YorN as tinyint = 0,
@DOB_YorN as tinyint = 0,
@CCHP_ID_YorN as tinyint = 0,
@RimsID_YorN as tinyint = 0,
@MediCalNum_YorN as tinyint = 0,
@ProvTaxID_YorN as tinyint = 0,
@ProvKeaneID_YorN as tinyint = 0,
@ProvVendoNum_YorN as tinyint = 0,
@LastName as char(50) = Null,
@FirstName as char(50) = Null,
@MRN as char(9) = Null,
@SSN as char(9) = Null,
@DOB as char(10) = Null,
@CCHP_ID as char(11) = Null,
@Rims_ID as char(10) = Null,
@MediCalNum as char(15) = Null,
@ProvTaxID as char(10) = Null,
@ProvKeaneID as char(10) = Null,
@ProvVendorNum as char(10) = Null
AS
Set NoCount On
CREATE TABLE [dbo].[#tmpPeopleOrgSearchResults]
(
[Source] [nchar] (1) Not NULL ,
[Key1] [nchar] (10) Not NULL ,
[Key2] [nchar] (5) NULL ,
[Key3] [nchar] (1) NULL ,
[lastname] [nchar] (50) NULL ,
[firstname] [nchar] (50) NULL ,
[mi] [nchar] (1) NULL ,
[city] [nchar] (50) NULL,
[phone] [nchar] (15) NULL,
[sex] [nchar] (1) NULL ,
[birthdate] [datetime] NULL ,
[ssn] [nchar] (9) NULL ,
[cchp_id] [nchar] (11) NULL,
[medical_num] [nchar] (15) NULL,
[provtax_id] [nchar] (10) NULL,
[provkeane_id] [nchar] (10) NULL,
[provvendor_num] [nchar] (10) NULL,
[keane_mrn] [nchar] (15) NULL
)
-- 1) Rims Members Process
if @RimsMems_YorN <> 0
Begin
Insert Into [dbo].[#tmpPeopleOrgSearchResults]
Select
'M' As [Source],
[enr_ssn] As Key1,
[enr_group] As Key2,
[mem_dep_code] As Key3,
[lastname] As lastname,
[firstname] As firstname,
'' As mi,
[city] As city,
[phone] as phone,
[sex] As sex,
[birthdate] As birthdate,
[enr_ssn] As ssn,
[cchp_member_id] As cchp_id,
[medi_cal_nbr] As medical_num,
'' As provtax_id,
'' As provkeane_id,
'' As provvendor_num,
'' As keane_mrn
From util..rimsmembers
Where
-- LastName
Case
When @LastName_YorN = 0 then '1'
When (@LastName is Null) then '1'
When (rtrim(lastname) like rtrim(@LastName) + '%') then '1'
Else '0'
End = '1'
And
-- FirstName
Case
When @FirstName_YorN = 0 then '1'
When (@FirstName is Null) then '1'
When (rtrim(firstname) like rtrim(@FirstName) + '%') then '1'
Else '0'
End = '1'
And
-- SSN
Case
When @SSN_YorN = 0 then '1'
When (enr_ssn is Null) then '1'
When (rtrim(enr_ssn) like rtrim(@SSN) + '%' ) then '1'
Else '0'
End = '1'
And
--CCHP_ID
Case
When @CCHP_ID_YorN = 0 then '1'
When (cchp_member_id is Null) then '1'
When (rtrim(cchp_member_id) like rtrim(@CCHP_ID) + '%' ) then '1'
Else '0'
End = '1'
And
-- MediCal Num
Case
When @MediCalNum_YorN = 0 then '1'
When (medi_cal_nbr is Null) then '1'
When (rtrim(medi_cal_nbr) like rtrim(@MediCalNum) + '%' ) then '1'
Else '0'
End = '1'
And
-- DOB
Case
When @DOB_YorN = 0 then '1'
When (birthdate is Null) then '1'
When (convert(char(10), birthdate,101) Like @DOB + '%') then '1'
Else '0'
End = '1'
End
-- 2) Rims Providers Process
if @RimsProv_YorN <> 0
Begin
Insert Into [dbo].[#tmpPeopleOrgSearchResults]
Select
'R' As [Source],
[pm_id_no] As Key1,
'' As Key2,
'' As Key3,
[pm_last_name] As lastname,
[pm_first_name] As firstname,
'' As mi,
[pm_city] As city,
[pm_phone] as phone,
'' As sex,
'' As birthdate,
'' As ssn,
[pm_id_no] As cchp_id,
[pm_medi_cal_id] As medical_num,
'' As provtax_id,
[pm_keane_id] As provkeane_id,
[pm_vendor_id] As provvendor_num,
'' As keane_mrn
From util..rimsproviders
Where
-- LastName
Case
When @LastName_YorN = 0 then '1'
When (@LastName is Null) then '1'
When (rtrim(pm_last_name) like rtrim(@LastName) + '%') then '1'
Else '0'
End = '1'
And
-- FirstName
Case
When @FirstName_YorN = 0 then '1'
When @FirstName is Null then '1'
When rtrim(pm_first_name) like rtrim(@FirstName) + '%' then '1'
Else '0'
End = '1'
And
-- CCHP_ID
Case
When @CCHP_ID_YorN = 0 then '1'
When (pm_id_no is Null) then '1'
When (rtrim(pm_id_no) like rtrim(@CCHP_ID) + '%' ) then '1'
Else '0'
End = '1'
And
-- MediCal Num
Case
When @MediCalNum_YorN = 0 then '1'
When (pm_medi_cal_id is Null) then '1'
When (rtrim(pm_medi_cal_id) like rtrim(@MediCalNum) + '%' ) then '1'
Else '0'
End = '1'
End
-- 3) Keane Patients Process
if @Keane_YorN <> 0
Begin
Insert Into [dbo].[#tmpPeopleOrgSearchResults]
Select
'K' As [Source],
[mrn] As Key1,
'' As Key2,
'' As Key3,
[pt_lname] As lastname,
[pt_fname] As firstname,
'' As mi,
[city] As city,
[phone] As phone,
[sex] As sex,
[dob] As birthdate,
[ssn] As ssn,
'' As cchp_id,
'' As medical_num,
'' As provtax_id,
'' As provkeane_id,
'' As provvendor_num,
[mrn] As keane_mrn
From keane..pat
Where
-- LastName
Case
When @LastName_YorN = 0 then '1'
When @LastName is Null then '1'
When rtrim(pt_lname) like rtrim(@LastName) + '%' then '1'
Else '0'
End = '1'
And
-- FirstName
Case
When @FirstName_YorN = 0 then '1'
When @FirstName is Null then '1'
When rtrim(pt_fname) like rtrim(@FirstName) + '%' then '1'
Else '0'
End = '1'
And
-- SSN
Case
When @SSN_YorN = 0 then '1'
When @SSN is Null then '1'
When rtrim(ssn) like rtrim(@SSN) + '%' then '1'
Else '0'
End = '1'
And
-- MRN
Case
When @MRN_YorN = 0 then '1'
When @MRN is Null then '1'
When rtrim(mrn) like rtrim(@MRN) + '%' then '1'
Else '0'
End = '1'
And
-- DOB
Case
When @DOB_YorN = 0 then '1'
When @DOB is Null then '1'
When rtrim(dob) like rtrim(@DOB) + '%' then '1'
Else '0'
End = '1'
End
-- 4) PMIS Process
if @PMIS_YorN <> 0
Begin
Insert Into [dbo].[#tmpPeopleOrgSearchResults]
Select
'P' As [Source],
p.[Prov_ID] As Key1,
'' As Key2,
'' As Key3,
[lastname] As lastname,
[firstname] As firstname,
'' As mi,
'' As city,
'' As phone,
'' As sex,
'' As birthdate,
'' As ssn,
i.cchp_vendor_id As cchp_id,
i.MediCal_ID As medical_num,
i.prov_tax_id As provtax_id,
i.Keane_ID As provkeane_id,
'' As provvendor_num,
'' As keane_mrn
From prov..provcore p
Left Outer Join prov..ProvIDS i On p.prov_ID = i.prov_ID
Where
-- LastName
Case
When @LastName_YorN = 0 then '1'
When @LastName is Null then '1'
When rtrim(lastname) like rtrim(@LastName) + '%' then '1'
Else '0'
End = '1'
And
-- FirstName
Case
When @FirstName_YorN = 0 then '1'
When @FirstName is Null then '1'
When rtrim(firstname) like rtrim(@FirstName) + '%' then '1'
Else '0'
End = '1'
End
-- 5) Department Staff Process
if @DeptStaff_YorN <> 0
Begin
Insert Into [dbo].[#tmpPeopleOrgSearchResults]
Select
'D' As [Source],
[pkid] As Key1,
'' As Key2,
'' As Key3,
[last_name] As lastname,
[first_name] As firstname,
'' As mi,
[city] As city,
[phone1] as phone,
[sex] As sex,
[dob] As birthdate,
[ssn] As ssn,
'' As cchp_id,
'' As medical_num,
'' As provtax_id,
'' As provkeane_id,
'' As provvendor_num,
'' As keane_mrn
From DeptStaff
Where
-- LastName
Case
When @LastName_YorN = 0 then '1'
When (@LastName is Null) then '1'
When (rtrim(last_name) like rtrim(@LastName) + '%') then '1'
Else '0'
End = '1'
And
-- FirstName
Case
When @FirstName_YorN = 0 then '1'
When (@FirstName is Null) then '1'
When (rtrim(first_name) like rtrim(@FirstName) + '%') then '1'
Else '0'
End = '1'
And
-- SSN
Case
When @SSN_YorN = 0 then '1'
When (ssn is Null) then '1'
When (rtrim(ssn) like rtrim(@SSN) + '%' ) then '1'
Else '0'
End = '1'
And
-- DOB
Case
When @DOB_YorN = 0 then '1'
When (dob is Null) then '1'
When (convert(char(10), dob,101) Like @DOB + '%') then '1'
Else '0'
End = '1'
End
select * from #tmppeopleorgsearchresults
Order By LastName, FirstName
GO
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply