My table have 10000 records and i create the 5 store procedures and my 5 applications exec the same time and each application load the records 2000 .it is working fine but the problem is when run 1 application it load the 2000 records and take 5 min then i run the 2 application it load 1900 records .Here the problem is 1 application are working with duplication records.How to load the 2 application 10000-2000=8000 in threading application.my sp like this below
;with P as
(
select *,ROW_NUMber() over(order by id) as rownum,
ntile(5) over(order by id) as thread
from [Records]
where not exists(
select name from
[Data]
where
Convert(varchar(10),date,103) = @date
and
[Records].name = [Data].name
)
select name from P where thread = 1
can you provide ddl and sample data? Your question is a little vague, but, if I had to guess, I would bet it's the not exists part. If there are duplicates and thread 1 loads Name1 for 1/1/2018 first, if Thread 2 has Name1 and 1/1/2018 it will ignore it
[Records] table is mater and [data] table save the data of records data like records table name1 results save in data table so that name1 is completed and go to the next name2 record and collect the data and save the data table........N names collated.
[Records] table data
nettoyants multi-usages
niveaux optiques
outils d'extraction
pierres à aiguiser
pinceaux
pinces à sertir
porte-fusibles à montage panneau
porte-fusibles en ligne
protection de tuyaux
réflecteurs
réflecteurs pour led
registres à décalage
relais temporisés
ressorts à gaz
rhéostats
rubans aluminium
I'd assume that part of your processing is removing records from the table, which makes the "bucket" any given row appears in change over time (because the results of Row_Number() change as the rows change)
The trick for this kind of thing is making a deterministic calculation for assigning data to a processing bucket, usually by using a field that is predictably incrementing through some simple maths, such as:
processing_bucket = id % 5
How well that distributes data depends a lot on your id being a continually incrementing integer and the overall workload associated with each item being roughly equal. There are smarter alternatives based on queues that can distribute work better in more complex scenarios, but it's hard to recommend where to go without knowing a lot more about the overall goal.
i am implement the id%5 but some records are not load the in thread(procedure).my sp like this
DECLARE @ThreadToFind as int
SET @ThreadToFind=1
;with P as
(
select *,ROW_NUMber() over(order by id) as rownum,
id%5 as thread
from [Records]
where not exists(select name from [Data] where Convert(varchar(10),date,103) = @date
and [Records].name = [Data].name
)
select name
from P
where thread = @ThreadToFind