Database

27-Oct-13 8:03

Hi Below query is taking 10 seconds to return 90 rows.

Using cte to get the dates of the last 90 days, then summing the production for each day (Alloocated is production qty) and DateCreated is a DateTime value that the units were produced.

So the output is the date of the last 90 days, and the total production for each day

Can anyone point out what I am doing wrong?

SQL

;WITH Dates AS
(
    SELECT GETDATE()as DateValue
    UNION ALL
    SELECT DateValue -1
    FROM Dates
    WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)
SELECT convert(nvarchar(10),DateValue,121) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(nvarchar(10),DateCreated,121) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(nvarchar(10),DateCreated,121)) 
as trk on trk.TrkDate = convert(nvarchar(10),DateValue,121)
group by DateValue, trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

Mycroft Holmes27-Oct-13 13:06

Mycroft Holmes

27-Oct-13 13:06

I don't think you can blame the CTE, I would look into all the convert dates, are you storing your dates as strings in vektronix?

Try changing all the string joins to date joins (eliminating the time component).

Try creating a temp table with the 90 dates instead of the CTE if that makes no difference. I use CTEs only as a last resort and this does not require one.

Never underestimate the power of human stupidity
RAH

Richard.Berry10027-Oct-13 18:54

27-Oct-13 18:54

Hi Mycroft.

The dates are stored as dates in the database. How can I do the joins eliminating the time component because that is the reason I did all that converting to nvarchar(10) was to eliminate the time part of the dates

Mycroft Holmes27-Oct-13 19:18

Mycroft Holmes

27-Oct-13 19:18

If you are using 2008 then cast/convert to DATE instead of DATETIME

SQL

DECLARE
	@D DATETIME
	
SET @D = GETDATE()

SELECT @D

SELECT CONVERT(DATE,@D)

Never underestimate the power of human stupidity
RAH

Richard.Berry10028-Oct-13 8:26

28-Oct-13 8:26

Thanks Mycroft = a bit faster but still slow for a mere 90 rows.

Bellow returns 10000 rows in under as second - so the problem must be in the second part

SQL

;WITH Dates AS
(
    SELECT GETDATE()as DateValue
    UNION ALL
    SELECT DateValue -1
    FROM Dates
    WHERE DateValue -1 >= dateadd(d,-10000,GETDATE())
)
select convert(date,DateValue) from Dates
OPTION (MAXRECURSION 32747)

This executes in under a second (This is basically the output I want, but I also want days where there was zero production hence the CTE)

SQL

select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated)

With the change to date as opposed to a datestring nvarchar(10) as you suggested, below has reduced from about 17 second to 5 seconds. But to be that seems terribly slow for 90 rows?

SQL

;WITH Dates AS
(
    SELECT GETDATE()as DateValue
    UNION ALL
    SELECT DateValue -1
    FROM Dates
    WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)

SELECT convert(date,DateValue) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '5'
group by convert(date,DateCreated)) 
as trk on trk.TrkDate = convert(date,DateValue)
group by DateValue, trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

Jörgen Andersson28-Oct-13 10:35

Jörgen Andersson

28-Oct-13 10:35

As the dateconversion seems to be the problem probably together with a bad plan I'd recommend that you minimize the number of conversions:

SQL

WITH Datetimes AS
(
    SELECT  GETDATE()as DateValue
    UNION ALL
    SELECT  DateValue -1
    FROM    Datetimes
    WHERE   DateValue -1 >= dateadd(d,-90,GETDATE())
)
,dates AS
(
    SELECT  DISTINCT convert(date,datevalue) AS ProdDate
    FROM    Datetimes
)
,trk AS
(
    SELECT  convert(date,DateCreated) as TrkDate
           ,allocated
    from    vektronix.[dbo].[Tracking] t
    where   [TrackingTypeId] = '5'
)
,trkgrouped as
(
    SELECT  trkdate
           ,SUM(allocated) as Tot
    FROM    trk
    GROUP BY trkdate
)

SELECT  ProdDate
       ,coalesce(Tot,0)as ProdQty
FROM    Dates D
left JOIN trkgrouped trk on trk.TrkDate = d.ProdDate
order by ProdDate desc
OPTION (MAXRECURSION 32747)

The purpose of the DISTINCT is to force the DatesCTE to be materialized.

But if you really want to boost the performance of the query you need to add convert(date,DateCreated) to vektronix.[dbo].[Tracking] as a calculated column and put an index on it.

The reasonable man adapts himself to the world; the unreasonable man adapts the world to himself.
Therefore all progress depends on the unreasonable man.
- George Bernard Shaw

Richard.Berry10028-Oct-13 18:39

What is the format of the connection string used for mysql database access?

28-Oct-13 18:39

Thanks Jorgen

Unfortunately it not my database to add columns.

I tried your query, but it was also slow, however that got me thinking more about the data I am querying. The vektronix table is actually quite large already after only a few months of production (about 8 rows for each unit produced) so I tried limiting the rows from vektronix in the where clause - that worked!

SQL

;WITH Dates AS
(
    SELECT GETDATE()as DateValue
    UNION ALL
    SELECT DateValue -1
    FROM Dates
    WHERE DateValue -1 >= dateadd(d,-90,GETDATE())
)

SELECT convert(date,DateValue) as ProdDate , coalesce(trk.Tot,0)as ProdQty
FROM Dates D
left join (select convert(date,DateCreated) as TrkDate ,SUM(allocated) as Tot from vektronix.[dbo].[Tracking] t
where [TrackingTypeId] = '1' and DateCreated >= dateadd(d,-90,GETDATE())
group by convert(date,DateCreated)) 
as trk on trk.TrkDate = convert(date,DateValue)
group by DateValue,trk.Tot
order by datevalue desc
OPTION (MAXRECURSION 32747)

Thanks for your help!

Count related

Corporal Agarn25-Oct-13 5:37

Corporal Agarn

25-Oct-13 5:37

In SQL Server 2008R2, I two tables:
T1.V1
1
2
3

T2.V1 T1.V2
1 A
2 B
3 A

I tried:

SQL

SELECT T1.V1, COUNT(T2.V2) OVER(PARTITION T2.V2) AS MyCount 
FROM T1 
INNER JOIN T2 ON T1.V1 = T2.V1

and got what asked for not what I wanted.

What I want is the number of V2's in T2.

Output was:
V1 MyCount
1 1
2 1
3 1

Wanted
V1 MyCount
1 2
2 1
3 2

I hope my simplification of the problem gives enough information.

Thanks

Re: Count related

Richard Deeming25-Oct-13 7:47

Richard Deeming

25-Oct-13 7:47

Not sure how you're getting to your "wanted" output, since each row in T1 is only related to a single row in T2.

The only way I can see you getting that result would be something like this:

SQL

SELECT
    T1.V1,
    COUNT(1) As MyCount
FROM
    T1
    INNER JOIN T2
    ON T1.V1 = T2.V1
    INNER JOIN T2 As X
    ON T2.V2 = X.V2
GROUP BY
    T1.V1

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Count related

Corporal Agarn25-Oct-13 8:13

Corporal Agarn

25-Oct-13 8:13

What I have used is a join to code does the count first. I was hoping for another way to do it.

Gbenbam24-Oct-13 15:59

Gbenbam

24-Oct-13 15:59

What is the format of the connection string used for Mysql access in PHP?

Re: What is the format of the connection string used for mysql database access?

PIEBALDconsult24-Oct-13 17:33

PIEBALDconsult

24-Oct-13 17:33

http://www.connectionstrings.com/mysql/[^]

Re: What is the format of the connection string used for mysql database access?

coded00725-Oct-13 3:17

coded007

25-Oct-13 3:17

SQL

Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Mani Prabhakar

LINQ syntax

vkEE24-Oct-13 3:24

vkEE

24-Oct-13 3:24

Hi,

For some reason no matter what I try, this syntax is erroring out.

var dispQuestions = (from m in demographicList.Where(o => o.customertype=="Patient")).ToList();

It complains having a select or group by.
I tried this as well, and it still errors.

var dispQuestions = (from m in demographicList.Where(o => o.customertype=="Patient")).Select().ToList();

any thoughts?

FREE Code Conversion VB6 ASP VB.NET C# ASP.NET C++ JAVA PHP DELPHI ColdFusion
HTML Marquee & its alternatives

thatraja24-Oct-13 3:37

thatraja

24-Oct-13 3:37

vkEmerson wrote:
I tried this as well, and it still errors.

What's that? always include those details in your questions. Besides you have posted your question in wrong forum. Try LINQ forum[^]. Now don't repost this question there, leave it now. Hereafter choose right forum.

thatraja

Nobody remains a virgin, Life screws everyone Sigh | :sigh:

vkEE24-Oct-13 3:58

vkEE

24-Oct-13 3:58

I could not find the LINQ forum, under the discussion. I will book mark the linq forum for future.

if I try to get

dispQuestions.Count

, there is an error message,

Error   5   Operator '>' cannot be applied to operands of type 'method group' and 'int'

Syntax wise, am I missing the format? I am used to ADO.Net, and new to Linq. Trying to figure the various way to write it.

Thanks.

Bernhard Hiller24-Oct-13 21:28

Bernhard Hiller

24-Oct-13 21:28

Try dispQuestions.Count() instead.

Problem Connecting To SQL from SOME Machines

Richard Deeming24-Oct-13 4:25

Richard Deeming

24-Oct-13 4:25

The problem here is that you're mixing up the query syntax and the method syntax[^]. When the compiler is complaining that you're missing a select, it means that a query-syntax query must end with a select statement, but you've tried to fix it by adding a call to the Select method.

Your query could be written in one of two ways:

// Query syntax:
var dispQuestions = (from m in demographicList where m.customertype == "Patient" select m).ToList();

// Method syntax:
var dispQuestions = demographicList.Where(o => o.customertype == "Patient").ToList();

I tend to prefer the second option, but it's a purely aesthetic choice.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Kevin Marois22-Oct-13 9:29

Kevin Marois

22-Oct-13 9:29

.Net WPF app connecting to SQL 2008 R2 running on Win Server 2008

This is a recent problem. Probably started in the last day or two. Not sure because I haven't tested on the server since...

I have a WPF app that runs on a Win 8 Tablet. I have the tablet on my desk. I also have a copy on my client's server in Canada.

From my development PC the app CAN connect to SQL on my server. I copy the app to the tablet and run it and it does NOT connect. I get:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) ---> System.ComponentModel.Win32Exception (0x80004005): The system cannot find the file specified

I get the same trying to run it from the client's server. Both the tablet and the copy on the client's server were connecting a few days ago.

The server's IP/Port are hardcoded in (to test), so that hasn't changed.

I checked all the user remote connection settings. Everything looks fine. The part that worries me is the "File not found" part. And why do I NOT get this connecting from my Dev pc??

I need some help.

Thanks

If it's not broken, fix it until it is

FREE Code Conversion VB6 ASP VB.NET C# ASP.NET C++ JAVA PHP DELPHI ColdFusion
HTML Marquee & its alternatives

thatraja22-Oct-13 22:36

thatraja

22-Oct-13 22:36

Bunch of reasons, here you go
Steps to troubleshoot SQL connectivity issues[^]
SQL Server 2005 Connectivity Issue Troubleshoot - Part I[^]
Troubleshoot Connectivity Issue in SQL Server 2005 - Part II[^]
Troubleshoot Connectivity Issue in SQL Server 2005 - Part III[^]

thatraja

Nobody remains a virgin, Life screws everyone Sigh | :sigh:

jschell23-Oct-13 9:48

jschell

23-Oct-13 9:48

So google for the following.

"Named Pipes Provider, error: 40"

That leads to the following

http://blogs.msdn.com/b/sql_protocols/archive/2007/03/31/named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server.aspx[^]

There are several reasons there and none of which should apply to you because, presumably, nothing changed.

However the nature of those tells you the problem - it could not establish the connection.
Now you might want to at least verify that someone wasn't bouncing the server when you had the problem. You can see that in the event log if SQL Server restarted.
If not then it is likely it is a network problem.

Bernhard Hiller23-Oct-13 20:43

Bernhard Hiller

23-Oct-13 20:43

"The server's IP/Port are hardcoded in (to test), so that hasn't changed."
And that's a public IP address somewhere on the web where it can be reached without restrictions? Or is it something like 192.168.0.42?