|
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
|
|
|
|
|
Ah.. So, your SQL server is not slow.
It is under a lot of load, too many users and an inefficient sproc. Remove as much of the functions as possible, stop formatting the fields in the database and do that from code.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Why are us using Express with 40 users?
Why are us taking a date and converting it to VARCHAR to compare?
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Let's try this again...
If the computer has ONLY 8 GB of physical memory available to it and you are allowing SQLServer to use over 214,000 GB of physical memory, where is it coming from?
From Microsoft:
Allowing SQL Server to use memory dynamically is recommended; however, you can set the memory options manually and restrict the amount of memory that SQL Server can access. Before you set the amount of memory for SQL Server, determine the appropriate memory setting by subtracting, from the total physical memory, the memory required for the OS and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to SQL Server.
What is the physical memory? 8 GB
How much does the OS need? Let's say 2 GB
How much is left for SQL? 6 GB
Set your SQLServer maximum memory to a more reasonable value and then report what happens.
|
|
|
|
|
Ok ,
set Server maximum memory=6 Gb. after that?
|
|
|
|
|
Set the maximum amount of memory and stop/start the SQLServer engine (or restart the computer).
Check system performance including Task Manager. How much memory is still available?
If SQLServer uses too much memory, the operating system is forced to use page files and thrash the hard drive.
|
|
|
|
|
Have you tried using SQL Profiler to determine which Stored Procedure or SQL statement is causing the issue ?
You may discover that the query could be improved by adding an index.
Good luck.
|
|
|
|
|
I will note that I have tested an application that was running 100 TPS (Transactions Per second) sustained on 32 bit machines where every transaction was hitting the database multiple times on a network with multiple client machines, a single app server and a single database server (again 32 bit with 2 CPUs.)
And I was never able to get the CPU on the database server machine to go above about 3%. The database was never even close to being a bottleneck.
I seriously doubt that 40 human users doing normal click through work would even be able to touch a 64 bit machine on even a adequately designed system.
Normal performance problems are as follows from most to least
1. Requirements (most impact)
2. Architecture/Design
3. Implementation
4. Technology (network, language, OS, etc.) (least impact)
Now as per the other threads very badly configuring a server could have an impact. But so can 1-3 in the above.
If it is the database server then reinstalling and accepting all of the defaults would return it to the base line and that would be more than adequate for very long time even with a substantial increase in human users. If of course 1-3 are done right.
|
|
|
|
|
Well I have seen some horror stored procs in my time and this one is particularly nasty.
Split the proc in multiples procedures to return 1 dataset each, multiple dataset can destroy the performance.
You have business logic in your stored proc (c.title and followupstatus should be reference/dimension tables)
You are formatting dates in the database, leave that to the client.
This looks like a complete horror - looks like you are storing date & time when you really only need the date and this kludge it trying to deal with it.
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))
Try using datetime data type, BETWEEN may be useful to you.
This seems to indicate you are storing your dates as varchar -
ORDER BY CONVERT(DATETIME, follow_up_date) DESC one of the most basic and expensive errors a database designer can make.
I don't think you can blame the database server if this is what you are asking it to deal with. When you have cleaned up the errors you should then use profiler to determine if indexes can improve the performance.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
how to create a chart from a database in android eclipse ..
how to create a chart from a database in android eclipse ..
Thanks.
please I'm the project as an example
thanks ...
|
|
|
|
|
A database does not create a chart, it only supplies the data to support the chart. So you need to get the charting help from the android forum.
If you need help with the database query, here is the correct place.
And if you think we will do your job for you by supplying a completed sample project you are sadly mistaken.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How do we open a .ndf file?
|
|
|
|
|
What is an ndf file?
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
|
Assuming this is a secondary data file for an MS SQL database[^], you don't. If it's not already opened by SQL, you attach the main .mdf file[^], and SQL will pick up the associated files at the same time.
If it's some other file format, you'll need to enlighten us.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hi everyone,
I'm strugling with a string function.
I have a PRODUCT field and it contains 4 kind of values:
a) 000000000000001234
b) 000000000000123456
c) 000000000001234567
d) 000000000123456789
I want the values to look like this:
[1] if the length of PRODUCT without zeros is smaller then
7 the output is:
a) 0001234
b) 0123456
[2] if the length of PRODUCT without zeros is equal to 7 then output is:
c) 1234567
[3] If the lengt of PRODUCT without zeros is equal to 9 then output is:
d) 123456789
The If statements [1] and [2] are taken care of with af function that I call in my statement:
ALTER FUNCTION [dbo].[LPAD]
(
-- Add the parameters for the function here
-- Test with data as nvarchar like in Staging
@SourceString nvarchar(MAX), --Varchar(MAX),
@FinalLength int,
@PadChar Char(1)
)
RETURNS nvarchar(MAX) --Varchar(MAX) --<Function_Data_Type, ,Int>
WITH SCHEMABINDING
AS
BEGIN
RETURN --<@ResultVar, sysname, @Result>
(Select REPLICATE(@PadChar,@FinalLength - Len(@SourceString)) + @SourceString)
END
SQL statement:
, CAST(RTRIM(dbo.LPAD(SUBSTRING(dbo.SD.PRODUCT, PATINDEX('%[^0]%',dbo.SD.PRODUCT+ ''), LEN(dbo.SD.PRODUCT)), 7, 0))
AS nvarchar(255)) AS PRODUCT
So if PRODUCT is smaller then 7 chars I need a zero to make a maximum length of 7 chars. The first 11 zero's need to dissapear and thats taken care of with the above function and statement.
It's the third statement that gives me a headache, I also want the PRODUCT with 9 chars without any leading zero.
Does anyone know how I can integrate the 3rd if please ???
Kind regards,
Ambertje
|
|
|
|
|
SELECT RIGHT('0000000' + @STR, CASE WHEN LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) < 7 THEN 7 ELSE LEN(CAST(CAST(@STR AS INT) AS NVARCHAR(MAX))) END )
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Hi Peter,
This isn't working, I get no values at all.
I think I didn't mention that I'm working with a View.
I used your solution like this:
,RIGHT('0000000' + dbo.SD.PRODUCT, CASE WHEN LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) < 7 THEN
7 ELSE LEN(CAST(CAST(dbo.SD.PRODUCT AS INT) AS NVARCHAR(MAX))) END)
|
|
|
|
|
This one will work assuming the product codes are always numeric:
DECLARE @INPUT NVARCHAR(MAX) = 'your product code with or without leading zeros goes here'
DECLARE @TMP_INPUT INT = CAST(@INPUT AS INT)
SELECT
CASE
WHEN LEN(CAST(@TMP_INPUT AS NVARCHAR)) < 7 THEN SUBSTRING(@INPUT, 1, 7)
ELSE CAST(@TMP_INPUT AS NVARCHAR)
END
Regards,
Johan
My advice is free, and you may get what you paid for.
|
|
|
|
|
am new to vb.net and trying to do some reporting but while trying to retrieve records i got problems in the format of the report.
I have the following tables in the database:
1. Exams
ExamType ExamId
EXAM I 1
EXAM II 2
EXAM III 3
2. SUBJECTS
SUBJECTID SUBJECTNAME
1 ENGLISH
2 GEOGRAPHY
3 MATHEMATICS
4 BIOLOGY
5 CHEMISTRY
6 PHYSICS
3. MARKS
STUDENTNO TOTALSCORE ENGLISH GEOGRAPHY MATHEMATICS BIOLOGY C.
20 240 50 60 70 60
21
23
34
56
...
now i wish to retrieve records in the format:
SUBJECTNAME Exam I Exam II Exam III Avg
ENGLISH 50 78 67
GEOGRAPHY 60 67
MATHEMATICS 70 56
BIOLOGY 60 90
CHEMISTRY -
PHYSICS -
Any idea on i can go about this?. any help will be appreciated
modified 21-Mar-15 13:18pm.
|
|
|
|
|
First thing first, redesign the marks table as follows:
studentno subjectid mark
20 1 50
20 2 60
20 3 70
20 4 60
21 1 70
... ... ...
The subjectid of the marks table will be the foreign key referencing the subjectid (primary key) of the subject table
The SQL query to retrieve the desire marks by subject by student will be:
SELECT s.subjectname, m.mark FROM marks m inner join subjects s
on m.subjectid = s.subjectid WHERE studentno = 20
Find out more on http://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html[^]
|
|
|
|
|
Unless you have omitted in your post something from your database-model, my first suggestion would be to rework your database-model a bit. You're missing some stuff there which would make a lot of sense:
Table ExamType
ExamTypeId (Primary Key)
ExamTypeName (Exam I / Exam II / Exam III)
Table Subject
SubjectId (Primary Key)
SubjectName (English / Geography / ...)
Table Student
StudentId (Primary Key)
StudentName
Table Exam
ExamId (Primary Key)
ExamTypeId (Foreign Key referencing ExamType.ExamTypeId)
SubjectId (Foreign Key referencing Subject.SubjectId)
ExamTakenDate
Table Mark
ExamId (Foreign Key referencing Exam.ExamId) \_ Primary Key
StudentId (Foreign Key referencing Student.StudentId) /
Score
Then you can get the marks with a query like this:
SELECT St.StudentName,
Sj.SubjectName,
Et.ExamTypeName,
Ex.ExamTakenDate,
Mk.Score
FROM Student AS St
JOIN Mark AS Mk ON St.StudentId = Mk.StudentId
JOIN Exam AS Ex ON Mk.ExamId = Ex.ExamId
JOIN ExamType AS Et ON Ex.ExamTypeId = Et.ExamTypeId
JOIN Subject AS Sj ON Ex.SubjectId = Sj.SubjectId
WHERE
StudentId = ...
ExamId = ...
SubjectId = ...
Edit: What you can read when following the link Peter Loew has posted in his answer is basically the reasoning for my suggested rework of your database-model.
Recursion: see Recursion.
modified 21-Mar-15 11:42am.
|
|
|
|
|
thanks for replying...i have tried and it assists. But sorry for this. I forgot something in the final report form
SUBJECTNAME Exam I Exam II Exam III Avg
ENGLISH 50 78 67
GEOGRAPHY 60 67
MATHEMATICS 70 56
BIOLOGY 60 90
CHEMISTRY -
PHYSICS -
Kindly assist...Will be grateful
modified 22-Mar-15 13:54pm.
|
|
|
|
|
Hi everybody.
My problem: I have a query and it takes too much time to fetch, when I use substr funtion to create an inner join between a table and a view... my code:
select *
from myview@rs T
inner join myTable D on T.CODE_ONE= substr(D.NUM,0,3) and T.CODE_TWO=substr(D.NUM,3,15)
where D.NUM='1344628596434'
The problem is:
on T.CODE_ONE= substr(D.NUM,0,3) and T.CODE_TWO=substr(D.NUM,3,15)
The query doesn't take too long time, when I do this:
select *
from myview@rs T
inner join myTable D on T.CODE_ONE= '134' and T.CODE_TWO='4628596434'
where D.NUM='1344628596434'
But I cannot use static values... thanks for the help
modified 20-Mar-15 16:03pm.
|
|
|
|
|
Ludwing RS wrote: I have a query and it takes too much time to fetch
How much time would be allowed?
There's no faster alternative to splitting a string, but it does count as a design-mistake. Each attribute in the tupel should be atomic. You should not need to separate the facts from a single field. They should have been two separate columns.
You "could" try to create an view that does the splitting, and making sure that those calculated columns are materialized. It would effectively move the extra processing required to when the view is created, rather then when the data is requested.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
