Database

Re: SQL Linq, select from select and join

22-Jul-15 6:02

I was thinking about that last night. Guess I should of made 1 giant context for the entire database?, or done a better job of planning by combining the cart and product tables.
Lesson learned.

Richard Deeming22-Jul-15 8:00

Richard Deeming

22-Jul-15 8:00

jkirkerx wrote:
Guess I should of made 1 giant context for the entire database?

That's the way I'd go. I'd only consider splitting them up if the tables in each context were completely separate, with no cross-context relationships, and would never need to be queried together.

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

Re: SQL Linq, select from select and join

jkirkerx22-Jul-15 9:04

Trouble working with dates again and union and sum

22-Jul-15 9:04

I just made 1 context file with everything in it.

Finally got the DateRanges working, was banging my head against the wall to figure it out.

I had to wrap the Where statement in (), in VB its different to compare.

Where (oh.OrderDate >= startDate And oh.OrderDate <= stopDate)

Hey thanks for your help!, that was hard to figure out.

jkirkerx20-Jul-15 13:08

Re: Trouble working with dates again and union and sum

20-Jul-15 13:08

So I wrote this to represent the TSQL below
Not sure where I messed up, but I think it's the sum at the end.
I got one result for $46.51, but it should of been $0.00, now I broke it.

Using context As New OrdersContext

  Dim DateStart As DateTime = DateTime.Today.AddDays(-1)
  Dim DateStop As DateTime = DateTime.Today.AddDays(+1)

  pValue = _
      (From oh In context.Order_History
      Where oh.OrderStatus = "COMPLETED" _
      And oh.OrderDate > DateStart _
      And oh.OrderDate < DateStop
      Select oh).Union _
     (From oc In context.Order_Completed
     Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
     Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
     Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
     Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
     Or oc.OrderStatus = "READY_TO_SHIP" _
     And oc.OrderDate > DateStart _
     And oc.OrderDate < DateStop
    Select oc).Sum(Function(m) m.GrandTotal)

End Using

To represent this

DECLARE @StartDate AS Date; 
DECLARE @StopDate AS Date; 
SET @StartDate = CONVERT(CHAR(10),GETDATE(),101); 
SET @StopDate = CONVERT(CHAR(10),DATEADD(d,1, GETDATE()),101); 
WITH sums AS ( 
   SELECT SUM(GrandTotal) GrandTotal 
   FROM CompletedOrdersHistory 
   WHERE OrderStatus='COMPLETED' 
   AND OrderDate >= @StartDate 
   AND OrderDate < @StopDate  
   UNION ALL 
   SELECT SUM(GrandTotal) GrandTotal 
   FROM CompletedOrders 
   WHERE OrderStatus = 'QUEUED_FOR_FULFILLMENT' 
   OR OrderStatus = 'OUT_FOR_FULFILLMENT' 
   OR OrderStatus = 'QUEUED_FOR_SHIPPING' 
   OR OrderStatus = 'OUT_FOR_PACKAGING' 
   OR OrderStatus = 'READY_TO_SHIP' 
   AND OrderDate >= @StartDate 
   AND OrderDate < @StopDate 
) 
SELECT Sum(GrandTotal) AS GrandTotal 
FROM sums

jkirkerx20-Jul-15 13:34

20-Jul-15 13:34

Got the union working, and sum, just have trouble with the date range.

pValue = _
                ((From oh In context.Order_History
                Where oh.OrderStatus = "COMPLETED" _
                And oh.OrderDate = DbFunctions.AddDays(DateTime.Now(), 0)
                Select oh.GrandTotal).Union _
                (From oc In context.Order_Completed
                Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
                Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
                Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
                Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
                Or oc.OrderStatus = "READY_TO_SHIP" _
                And oc.OrderDate = DbFunctions.AddDays(DateTime.Now(), 0)
                Select oc.GrandTotal)).Sum()

make nullable type

jkirkerx20-Jul-15 13:59

20-Jul-15 13:59

Well I'm closer now. I suspect that I have to redesign this Linq in order for it to work.
More of a From Sum Select then where

The cast to value type 'System.Decimal' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

Dim pValue As Nullable(Of Decimal) = 0

        Using context As New OrdersContext

            Dim DateStart As DateTime = DateTime.Today.AddDays(-1)
            Dim DateStop As DateTime = DateTime.Today.AddDays(+1)

            pValue = _
                ((From oh In context.Order_History
                Where oh.OrderDate >= DateStart _
                And oh.OrderDate <= DateStop _
                And oh.OrderStatus = "COMPLETED" _
                Select oh.GrandTotal).Union _
                (From oc In context.Order_Completed
                Where oc.OrderDate >= DateStart _
                And oc.OrderDate <= DateStop _
                And oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
                Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
                Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
                Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
                Or oc.OrderStatus = "READY_TO_SHIP"
                Select oc.GrandTotal)).Sum()

        End Using

        Return pValue

[Got It]

jkirkerx21-Jul-15 9:58

The above post is incorrect - SQL Linq, Date Range, Union in VB

21-Jul-15 9:58

Finally

Using context As New OrdersContext

            pValue = _
                (
                    From oh In context.Order_History
                    Where oh.OrderDate >= DateStart _
                    And oh.OrderDate <= DateStop _
                    And oh.OrderStatus = "COMPLETED" _
                    Select oh.GrandTotal
                ).Union _
                (
                    From oc In context.Order_Completed
                    Where oc.OrderDate >= DateStart _
                    And oc.OrderDate <= DateStop _
                    And oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
                    Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
                    Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
                    Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
                    Or oc.OrderStatus = "READY_TO_SHIP"
                    Select oc.GrandTotal
                ).DefaultIfEmpty().Sum()

        End Using

jkirkerx22-Jul-15 8:54

Re: The above post is incorrect - SQL Linq, Date Range, Union in VB

22-Jul-15 8:54

After testing with a real dataset, I was getting the wrong values.
For doing this in VB, you have to wrap the date range in (), in order to properly evaluate it.
So this produces the correct result, even if the value is 0.
I was banging my head against the wall trying to figure this one out.

Thanks to Richard Deeming for setting me straight on the DateTime.

Dim pValue As Decimal = 0

        Dim baseDate As DateTime = DateTime.Today()
        Dim dateStart As New DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 0, 0, 0, 0)
        Dim dateStop As New DateTime(baseDate.Year, baseDate.Month, baseDate.Day, 23, 59, 59, 999)

        Using context As New hx5Context

            pValue = _
            (
                From oh In context.Order_History
                Where (oh.OrderDate = dateStart And oh.OrderDate <= dateStop) _
                And Not (oh.OrderStatus = "ORDER_CANCELED")
                Select oh.GrandTotal
            ).Union _
            (
                From oc In context.Order_Completed
                Where (oc.OrderDate >= dateStart And oc.OrderDate <= dateStop) _
                And
                (
                    oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
                    Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
                    Or oc.OrderStatus = "QUEUED_FOR_SHIPPING" _
                    Or oc.OrderStatus = "OUT_FOR_PACKAGING" _
                    Or oc.OrderStatus = "READY_TO_SHIP" _
                )
                Select oc.GrandTotal
            ).DefaultIfEmpty().Sum()

        End Using

        Return pValue

Mycroft Holmes22-Jul-15 14:15

Mycroft Holmes

22-Jul-15 14:15

jkirkerx wrote:
Thanks to Richard Deeming for setting me straight on the DateTime.

You can up vote his responses to show appreciation (up/down arrows appear on the left of the response).

Never underestimate the power of human stupidity
RAH

Re: The above post is incorrect - SQL Linq, Date Range, Union in VB

jkirkerx23-Jul-15 6:38

Re: The above post is incorrect - SQL Linq, Date Range, Union in VB

23-Jul-15 6:38

I haven't been able to do that in years, its like they took that away from me. Seriously, I don't get the vote interface when I log in.

Brisingr Aerowing23-Jul-15 7:37

Brisingr Aerowing

23-Jul-15 7:37

Hover your mouse pointer over a message and two arrows should appear on the left side. The green upward pointing one is an upvote. The old voting system was removed due to abuse, but now a spam/abuse vote is used as a one vote by wuck fits and trolls. No real change there.

What do you get when you cross a joke with a rhetorical question?

The metaphorical solid rear-end expulsions have impacted the metaphorical motorized bladed rotating air movement mechanism.

Do questions with multiple question marks annoy you???

Re: The above post is incorrect - SQL Linq, Date Range, Union in VB

jkirkerx23-Jul-15 8:21

23-Jul-15 8:21

I've seen those, never bothered to click on them.
Thanks
Guess you need to delete the message to keep it secret secret!

Database management

Otekpo Emmanuel19-Jul-15 9:22

Otekpo Emmanuel

19-Jul-15 9:22

Hello friends! How is the day going? I need opinions in this case. I'm working on a social network site and as you all know there are many modules involved e.g friends, chat, messages table etc. My question is should I create database for each e.g chat db, messages db, friends db or I should create one database then create table for each of them e.g tblchat, tblmessages etc. Which of the option above is ok when managing social network?

PIEBALDconsult19-Jul-15 9:40

PIEBALDconsult

19-Jul-15 9:40

While possible, I expect that any of those options will prove equally challenging to you.

Richard MacCutchan19-Jul-15 22:00

Richard MacCutchan

19-Jul-15 22:00

You need to decide where one set of records needs a connection or relationship to another. If they have a relationship then it makes sense for them to be in separate tables of a single database.

Mycroft Holmes19-Jul-15 22:32

Mycroft Holmes

19-Jul-15 22:32

If you are expecting extreme volumes you should investigate the storage methods specifically for that environment. Otherwise have them all reside in the same database.

If you need separation then use schemas, this will make it easier to split the database in the future if your client base grows significantly.

Never underestimate the power of human stupidity
RAH

Wendelius20-Jul-15 6:50

Wendelius

20-Jul-15 6:50

As already answered the man criteria would be; Is there a relation between the data? If there is, a single database is the most logical choice.

However, there is also other things to consider such as maintenance, backups and so on. So even if the data isn't related, from the maintenance point of view it could simplify the situation if all the data is in one place. Of course if the amount of data grows huge over time you may have to separate it but in the beginning it would make sense to start small.

Database management

Otekpo Emmanuel19-Jul-15 9:38

Otekpo Emmanuel

19-Jul-15 9:38

ZurdoDev22-Jul-15 9:48

ZurdoDev

22-Jul-15 9:48

Where did your question go? Confused | :confused:

There are only 10 types of people in the world, those who understand binary and those who don't.

large database

V.16-Jul-15 21:46

16-Jul-15 21:46

I'm using a postgresql database and managing it becomes harder and harder due to the sheer size of it.
the largest table contains about 2.5-3 billion records and there are some tables reaching about 10 million records. Database size is about 1.2 TB on disk. I believe the large table (and the tables around it) is the most problematic since there are (very roughly) 10 million write statements a day.

I have had issues with the transaction id's overflowing. Luckily by now I know the cure, but I would like to avoid this issue (and any other issue!) if possible. Especially since the database is expected to grow even further. VACUUM takes like forever on that large table.

I did a search on managing large databases, but most results are for other databases. Only found a not so helpful presentation on slideshare.

If anyone has good (practical) tips about keeping such a large database into good shape, please let me know.
(I hope I explained things well enough)

V.

(MQOTD rules and previous solutions)

Wendelius16-Jul-15 22:03

Wendelius

16-Jul-15 22:03

With that amount of data, I take it you already know enough about indexing etc so let's not go to those.

Depending what problem you're solving the technique might be different. Based on your description I'd concentrate on partitioning the large table (or tables) first. Partitioning would help you to isolate the hot spot of the table to a significantly smaller amount of rows so that changes in indexes and on disk would affect a considerable smaller amount of data.

For more information have a look at http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html[^]

As a side note, I hate to say this but I would also consider changing the database product Smile | :)

V.16-Jul-15 22:10

16-Jul-15 22:10

Oracle? I know there is a free Oracle version (and a free SQL-Server version), but I'm not sure they'll do the job. Paying for it is a no go here (has to be as cheap as possible), so I'm down with or MySQL or PostgreSQL and I'll take the latter any day Wink | ;-)

.

Indexing, I'm not really an expert, but the indexes are limited to 2 (excluding the implicit index on the primary key). So if you have tips there, please shoot.

For this application I'm the designer, architect, tester, developer and, you guessed it, DBA despite my limited knowledge of being a 'real' dba. (there are little or no people doing better at our company so no choice there)

I'll have a look at partitioning.

[EDIT]Ooh, and thanks ! Smile | :)

[/EDIT]

V.

(MQOTD rules and previous solutions)

Wendelius16-Jul-15 22:20

Wendelius

16-Jul-15 22:20

V. wrote:
I know there is a free Oracle version (and a free SQL-Server version), but I'm not sure they'll do the job

The free one, no. It wouldn't be sufficient because of the limitations for the database size etc. So you'd have to go for the paid one...

One interesting option could be https://mariadb.org/[^]. If you have time at some point, have a look. Smile | :)

Mycroft Holmes17-Jul-15 14:02

Mycroft Holmes

17-Jul-15 14:02

I think your strategy on indexing is probably a good one, you can kill a database with too many indexes...

As Mika suggested partitioning is going to be your best bet, but you should also look at archiving (a more drastic form of partitioning).

You should look at just what you are getting out of all those records and what use they are being put to! Is it possible to create a highly summarised reporting database.

Never underestimate the power of human stupidity
RAH