Click here to Skip to main content
15,867,568 members
Home / Discussions / Database
   

Database

 
AnswerRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Sandeep Tripathi25-Mar-15 1:10
Sandeep Tripathi25-Mar-15 1:10 
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Eddy Vluggen25-Mar-15 1:28
professionalEddy Vluggen25-Mar-15 1:28 
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Sandeep Tripathi25-Mar-15 1:31
Sandeep Tripathi25-Mar-15 1:31 
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Krishna Murthy G25-Mar-15 1:40
professionalKrishna Murthy G25-Mar-15 1:40 
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Sandeep Tripathi25-Mar-15 1:46
Sandeep Tripathi25-Mar-15 1:46 
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Eddy Vluggen25-Mar-15 2:04
professionalEddy Vluggen25-Mar-15 2:04 
AnswerRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Tim Carmichael25-Mar-15 2:01
Tim Carmichael25-Mar-15 2:01 
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Sandeep Tripathi25-Mar-15 2:16
Sandeep Tripathi25-Mar-15 2:16 
40 Users used every time.
Maximum Memory ?


I am sending proc who is working very slow .

/*


exec Visa_new_pending_followup_sel_final_new 'PCOMP/000002','','','','','',''


*/
CREATE PROC Visa_new_pending_followup_sel_final_new (@Comp_code VARCHAR(20),
@username VARCHAR(50),
@country VARCHAR(50) = NULL,
@username_S VARCHAR(100),
@adv_search VARCHAR(5) = NULL,
@yrs VARCHAR(5) = NULL,
@month VARCHAR(3) = NULL)
AS
BEGIN
SET nocount ON;

DECLARE @user_type VARCHAR(2)

SELECT @user_type = super_user
FROM crm_login_reg_tbl (nolock)
WHERE username = @username
AND company_code = @Comp_code

IF Isnull(Len(Ltrim(Rtrim(@username_S))), 0) = 0
BEGIN
SELECT @username_S = NULL
END

DECLARE @temp TABLE
(
username VARCHAR(200),
lead_code VARCHAR(200),
acc_code VARCHAR(200),
acc_name VARCHAR(200),
case_code VARCHAR(200),
subject VARCHAR(500),
follow_up_code VARCHAR(200),
follow_up_date_ DATETIME,
follow_up_time VARCHAR(10),
followup_status_desc VARCHAR(100),
creation_date VARCHAR(20),
created_by VARCHAR(100),
ref_type VARCHAR(100),
cont_code VARCHAR(100),
deadline_status VARCHAR(1),
deadline_date_ DATETIME,
deadline_reason VARCHAR(1000)
)

IF ( @user_type IN ( 'G' ) )
BEGIN
INSERT INTO @temp
SELECT a.username,
a.lead_code,
a.acc_code,
CONVERT(VARCHAR(20), CASE
WHEN a.acc_code <> '' AND a.acc_code IS NOT NULL THEN ( Upper(b.acc_name) )
WHEN a.lead_code <> '' AND a.lead_code IS NOT NULL THEN
(
Upper( Isnull(( CASE WHEN c.title = 'M' THEN 'Mr.'
WHEN c.title = 'S' THEN 'Ms.'
WHEN c.title = 'R' THEN 'Mrs.'
WHEN c.title = 'D' THEN 'Dr.'
WHEN c.title = 'P' THEN 'Prof.' END ), '') + ' ' +
Isnull(c.first_name, '') + ' ' + Isnull(c.last_name, '')) )
END) AS acc_name,
a.case_code,
CASE WHEN Len(a.subject) > 60 THEN CONVERT(VARCHAR(60), a.subject) + '...' ELSE a.subject END AS subject,
a.follow_up_code,
a.follow_up_date,
a.follow_up_time,
followup_status_desc = CASE
WHEN a.followup_status = 'K' THEN 'Email Sent'
WHEN a.followup_status = 'N' THEN 'None'
WHEN a.followup_status = 'D' THEN 'Call Latter'
WHEN a.followup_status = 'Q' THEN 'Lead Assign'
WHEN a.followup_status = 'S' THEN 'Lead Closed'
WHEN a.followup_status = 'A' THEN 'Not Reachable'
WHEN a.followup_status = 'OL' THEN 'Online'
WHEN a.followup_status = 'R' THEN 'Quotation Sent'
WHEN a.followup_status = 'H' THEN 'Not interested'
WHEN a.followup_status = 'F' THEN 'Get Back After Reading Brochure'
WHEN a.followup_status = 'G' THEN 'Will Discuss'
WHEN a.followup_status = 'C' THEN 'Not At Home'
WHEN a.followup_status = 'T' THEN 'Lead Re-open'
WHEN a.followup_status = 'J' THEN 'Not Eligible'
WHEN a.followup_status = 'O' THEN 'Order Sent'
WHEN a.followup_status = 'E' THEN 'Will Come And Meet'
WHEN a.followup_status = 'P' THEN 'Invoice Sent'
WHEN a.followup_status = 'L' THEN 'Get Back Later'
WHEN a.followup_status = 'M' THEN 'Duplicate Enquiry'
WHEN a.followup_status = 'B' THEN 'Wrong No'
WHEN a.followup_status = 'V' THEN 'Email Change'
WHEN a.followup_status = 'I' THEN 'Address Taken'
WHEN a.followup_status = 'U' THEN 'Quotation Approved'
END,
CONVERT(VARCHAR(17), a.creation_date, 113) AS creation_date,
( D.first_name + ' ' + D.last_name ) AS created_by,
a.ref_type,
a.cont_code,
deadline_status,
deadline_date,
deadline_reason
FROM visa_new_lead_follow_up_tbl a (nolock)
LEFT OUTER JOIN crm_new_acc_tbl b (nolock)
ON b.comp_code = a.company_code
AND b.acc_code = a.acc_code
LEFT OUTER JOIN crm_new_lead_tbl c (nolock)
ON c.comp_code = a.company_code
AND c.lead_code = a.lead_code
LEFT OUTER JOIN crm_login_reg_tbl D (nolock)
ON D.username = a.created_by
WHERE a.company_code = @Comp_code
AND a.compl_status = 'O'
AND a.username = Isnull(@username_S, a.username)
AND subject IS NOT NULL
ORDER BY CONVERT(DATETIME, follow_up_date) DESC
END

IF ( @user_type IN ( 'SA', 'A' ) )
BEGIN
INSERT INTO @temp
SELECT a.username,
a.lead_code,
a.acc_code,
CONVERT(VARCHAR(20), CASE
WHEN a.acc_code <> '' AND a.acc_code IS NOT NULL THEN ( Upper(b.acc_name) )
WHEN a.lead_code <> '' AND a.lead_code IS NOT NULL THEN
(
Upper( Isnull(( CASE WHEN c.title = 'M' THEN 'Mr.'
WHEN c.title = 'S' THEN 'Ms.'
WHEN c.title = 'R' THEN 'Mrs.'
WHEN c.title = 'D' THEN 'Dr.'
WHEN c.title = 'P' THEN 'Prof.' END ), '') + ' ' +
Isnull(c.first_name, '') + ' ' + Isnull(c.last_name, '')) )
END) AS acc_name,
a.case_code,
CASE
WHEN Len(a.subject) > 60 THEN
CONVERT(VARCHAR(60), a.subject)
+
'...'
ELSE a.subject
END AS subject,
a.follow_up_code,
a.follow_up_date,
a.follow_up_time,
followup_status_desc = CASE
WHEN a.followup_status = 'K' THEN 'Email Sent'
WHEN a.followup_status = 'N' THEN 'None'
WHEN a.followup_status = 'D' THEN 'Call Latter'
WHEN a.followup_status = 'Q' THEN 'Lead Assign'
WHEN a.followup_status = 'S' THEN 'Lead Closed'
WHEN a.followup_status = 'A' THEN 'Not Reachable'
WHEN a.followup_status = 'OL' THEN 'Online'
WHEN a.followup_status = 'R' THEN 'Quotation Sent'
WHEN a.followup_status = 'H' THEN 'Not interested'
WHEN a.followup_status = 'F' THEN 'Get Back After Reading Brochure'
WHEN a.followup_status = 'G' THEN 'Will Discuss'
WHEN a.followup_status = 'C' THEN 'Not At Home'
WHEN a.followup_status = 'T' THEN 'Lead Re-open'
WHEN a.followup_status = 'J' THEN 'Not Eligible'
WHEN a.followup_status = 'O' THEN 'Order Sent'
WHEN a.followup_status = 'E' THEN 'Will Come And Meet'
WHEN a.followup_status = 'P' THEN 'Invoice Sent'
WHEN a.followup_status = 'L' THEN 'Get Back Later'
WHEN a.followup_status = 'M' THEN 'Duplicate Enquiry'
WHEN a.followup_status = 'B' THEN 'Wrong No'
WHEN a.followup_status = 'V' THEN 'Email Change'
WHEN a.followup_status = 'I' THEN 'Address Taken'
WHEN a.followup_status = 'U' THEN 'Quotation Approved'
END,
CONVERT(VARCHAR(17), a.creation_date, 113) AS creation_date,
( D.first_name + ' ' + D.last_name ) AS created_by,
a.ref_type,
a.cont_code,
deadline_status,
deadline_date,
deadline_reason
FROM visa_new_lead_follow_up_tbl a (nolock)
LEFT OUTER JOIN crm_new_acc_tbl b (nolock)
ON b.comp_code = a.company_code
AND b.acc_code = a.acc_code
LEFT OUTER JOIN crm_new_lead_tbl c (nolock)
ON c.comp_code = a.company_code
AND c.lead_code = a.lead_code
LEFT OUTER JOIN crm_login_reg_tbl D (nolock)
ON D.username = a.created_by
WHERE a.company_code = @Comp_code
AND a.compl_status = 'O'
AND a.username = Isnull(@username_S, a.username)
AND subject IS NOT NULL
ORDER BY CONVERT(DATETIME, follow_up_date) DESC
END

SELECT acc_name,
subject,
followup_status_desc,
follow_up_time,
created_by,
creation_date,
CONVERT(VARCHAR(15), a.follow_up_date_, 103) AS follow_up_date

FROM @temp A
WHERE ( ( CONVERT(NVARCHAR(10), a.follow_up_date_, 121) + ' ' + CONVERT(CHAR(5), Cast(a.follow_up_time AS DATETIME), 108) ) < ( CONVERT(VARCHAR(16), Getdate(), 121) ) )
AND ( CONVERT(NVARCHAR(max), a.follow_up_date_, 101) + ' ' + CONVERT(NVARCHAR(max), Substring(a.follow_up_time, 1, 5), 108 ) ) <= ( CONVERT(VARCHAR(16), Getdate(), 120) )

SELECT acc_name,
subject,
followup_status_desc,
follow_up_time,
created_by,
creation_date,
CONVERT(VARCHAR(15), a.follow_up_date_, 103) AS follow_up_date

FROM @temp A
WHERE ( ( CONVERT(NVARCHAR(10), a.follow_up_date_, 121) + ' ' + CONVERT(CHAR(5), Cast(a.follow_up_time AS DATETIME), 108) ) > ( CONVERT(VARCHAR(16), Getdate(), 121) ) )

SELECT acc_name,
subject,
followup_status_desc,
follow_up_time,
created_by,
creation_date,
CONVERT(VARCHAR(15), a.follow_up_date_, 103) AS follow_up_date

FROM @temp A
WHERE CONVERT(VARCHAR(10), a.follow_up_date_, 111) =
CONVERT(VARCHAR(10), Getdate(), 111)

SELECT acc_name,
subject,
followup_status_desc,
follow_up_time,
created_by,
creation_date,
deadline_reason,
CONVERT(VARCHAR(15), a.follow_up_date_, 103) AS follow_up_date,
CONVERT(VARCHAR(15), a.deadline_date_, 103) AS deadline_date

FROM @temp A
WHERE deadline_status = 'A'

SET nocount OFF
END
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Eddy Vluggen25-Mar-15 3:11
professionalEddy Vluggen25-Mar-15 3:11 
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Corporal Agarn25-Mar-15 4:00
professionalCorporal Agarn25-Mar-15 4:00 
AnswerRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Tim Carmichael25-Mar-15 2:33
Tim Carmichael25-Mar-15 2:33 
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Sandeep Tripathi25-Mar-15 2:43
Sandeep Tripathi25-Mar-15 2:43 
GeneralRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Tim Carmichael25-Mar-15 2:52
Tim Carmichael25-Mar-15 2:52 
AnswerRe: Sql server 2008 R2 Express Edition working very slow why? Pin
David Mujica25-Mar-15 3:35
David Mujica25-Mar-15 3:35 
AnswerRe: Sql server 2008 R2 Express Edition working very slow why? Pin
jschell25-Mar-15 11:50
jschell25-Mar-15 11:50 
AnswerRe: Sql server 2008 R2 Express Edition working very slow why? Pin
Mycroft Holmes25-Mar-15 15:35
professionalMycroft Holmes25-Mar-15 15:35 
Questionhow to create a chart from a database in android eclipse .. please help me :D Pin
Pungkas Soebarkah24-Mar-15 23:24
Pungkas Soebarkah24-Mar-15 23:24 
AnswerRe: how to create a chart from a database in android eclipse .. please help me :D Pin
Mycroft Holmes25-Mar-15 15:16
professionalMycroft Holmes25-Mar-15 15:16 
QuestionNDF File Pin
Member 1133602724-Mar-15 8:45
Member 1133602724-Mar-15 8:45 
QuestionRe: NDF File Pin
ZurdoDev24-Mar-15 8:51
professionalZurdoDev24-Mar-15 8:51 
AnswerRe: NDF File Pin
Richard Deeming24-Mar-15 9:15
mveRichard Deeming24-Mar-15 9:15 
AnswerRe: NDF File Pin
Richard Deeming24-Mar-15 9:18
mveRichard Deeming24-Mar-15 9:18 
QuestionHelp needed on string function Pin
Ambertje24-Mar-15 6:01
Ambertje24-Mar-15 6:01 
AnswerRe: Help needed on string function Pin
Kornfeld Eliyahu Peter24-Mar-15 7:42
professionalKornfeld Eliyahu Peter24-Mar-15 7:42 
GeneralRe: Help needed on string function Pin
Ambertje24-Mar-15 23:43
Ambertje24-Mar-15 23:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.