|
SELECT * FROM DeviceLogs GROUP BY LogDate
this gives me error "because it is not contained in either an aggregate function or the GROUP BY clause"
|
|
|
|
|
Well you select all columns and group by LogDate.
Group by makes a single row out of all the rows that have the same LogDate in this example.
But what information is it supposed to put in ?
That's what aggregate functions are for.
However I think you should group by name, where LogDate=the day you want to select by.
You could also group by LogDate and name.
Anything that isn't in group by must be in an aggregate function.
So you have to decide which time you want to have selected (from what I deducted, first log ? ).
A bit unrelated to your question, but why do you have ID's that repeat ?
|
|
|
|
|
Are your Logdates varchars?
Every day, thousands of innocent plants are killed by vegetarians.
Help end the violence EAT BACON
|
|
|
|
|
Would this help?
SELECT ID, MIN(LogDate) AS LogDate, Name, Department
FROM yourtable
GROUP BY ID, Name, Department;
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
SELECT d.ID, d.LogDate, d.Name, d.Department
FROM DeviceLogs d, (SELECT MIN(LogDate) FROM DeviceLogs GROUP BY Name) s
WHERE s.MIN(LogDate) = d.LogDate
ORDER BY d.LogDate;
SELECT ID, MIN(LogDate) AS LogDate, Name, Department
FROM yourtable
GROUP BY ID, Name, Department;
|
|
|
|
|
|
Hi All,
I have written a small sql script to drop a table and recreate it but it is failing, I tried to drop all existing indexes on the table and column then trying to drop the column, but still fails by saying there is one constraint that's missing, and I don't know why but that constraint doesn't exist in database.
Here is the script that I am writing
DECLARE @ConstraintName nvarchar(200);
DECLARE @TableName nvarchar(500)='ApplicationData';
DECLARE @ColumnName nvarchar(500)='ReportingFpl';
SET NOCOUNT ON
SET xact_abort ON
WHILE 0=0 BEGIN
SET @constraintName = (
SELECT TOP 1 constraint_name
FROM information_schema.constraint_column_usage
WHERE table_name = @tableName and column_name = @columnName );
IF @constraintName is null BREAK;
EXEC ('alter table "'+@tableName+'" drop constraint "'+@constraintName+'"');
END
ALTER TABLE dbo.ApplicationData DROP COLUMN ReportingFpl;
GO
Error Message
Msg 5074, Level 16, State 1, Line 16
The index 'IX_ApplicationData_ApplicationId-ReportingFpl_PriorityPoints-ContractorPriorityPoints' is dependent on column 'ReportingFpl'.
Msg 4922, Level 16, State 9, Line 16
ALTER TABLE DROP COLUMN ReportingFpl failed because one or more objects access this column.
But the constraint 'IX_ApplicationData_ApplicationId-ReportingFpl_PriorityPoints-ContractorPriorityPoints' doesn't exist in the constraint table at all.
Can anybody please help me in resolving this issue, any link, any suggestion or code snippet helps greatly.
Thanks in advance.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
Hello sir,
I have dedicated server with 8 Gb RAM.I have developed the CRM when multiple user working at a time Sql server working very slow task is going on suspended.
Sql server 2008 R2 Express Edition
Database Size=6Gb
Maximum Memory=2147483647 Mb
Minimum Memory Per query=1024 kb
Pls.... suggest
|
|
|
|
|
Is it a 64-bit version? Using a 64-bit OS?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Yes it's 64 bit OS and Microsoft SQL Server Express Edition with Advanced Services (64-bit)
|
|
|
|
|
How did you determine it is "slow"?
Do you have an example query executed on the database-server itself?
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
When multiple user working at a time.Maximum task is suspended for this reason sql fetch data very slow.
|
|
|
|
|
1. Check for min and max memory configuration
2. Check is your procedure is creating any locks
Krishna Murthy G
|
|
|
|
|
Sql server
Maximum Memory=2147483647 Mb
Manimum Memory=0 Mb
Minimum Memory Per query=1024 kb
and procedure is not creating any locks.
|
|
|
|
|
How many users? If they're all hitting a large table without index, then I'd expect it to perform slowly. Can you give us an example of a typical query that is slow?
How many of those are reads and how many updates/inserts? The latter does create a lock, and would require updating of the index.
Needs more details 
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Sandeep Tripathi wrote: 8 Gb RAM
Sandeep Tripathi wrote: Maximum Memory=2147483647 Mb
Why is the maximum memory SQLServer is allowed to use higher than the available memory?
Reduce the maximum memory SQLServer can use.
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
