Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    19

    Unanswered: Creating a specific table

    Hi there,

    I'm working on a project that I've picked up again after some time. The data had to be altered slightly, so some of the original tables I used were replaced with new data, though used the same name.

    Now I wish to calculate the same data as before using these new tables, but I find myself having trouble getting the originally created tables re-created.

    Short summary of computations:
    - I have two data-tables, SP and TY.
    - I wish to calculate some returns from these (thus listing them with their values along with their values "lagged 1 time" in order to calculate the returns from this)
    - Via these returns I'll calculate some key-numbers.

    The problem:
    - During these computations, I recall creating a view last time, which was used in the proces of creating a new table, combining the original data-tables. This table is called ReturnTotal and is the one I'm having trouble creating. The code for the whole thing looks as follows:

    ----------------------------------

    Select * From SP
    inner join TY
    on SPDATE = TYDATE and SPTIME = TYTIME

    select * from vwspty

    Select * into SPTY from vwspty


    Select a.*, b.ObsTime as PrevObsTime, b.SPOPEN as PrevSPOPEN, b.SPCLOSE as PrevSPCLOSE, b.TYOPEN as PrevTYOPEN, b.TYCLOSE as PrevTYCLOSE, log(a.SPCLOSE)-log(b.SPCLOSE) as SPreturn, log(a.TYCLOSE)-log(b.TYCLOSE) as TYreturn
    into ReturnTotal
    from spty a ,spty b
    where b.obstime=(select max(obstime) from spty where obstime<a.obstime)

    Select * From ReturnTotal
    where datediff(hour, PrevObsTime, ObsTime) < 8

    b

    select *, covSPTY/sqrt(RVSP*RVTY) as CorSPTY from
    (
    Select
    sum(SPreturn*SPreturn) as RVSP,
    sum(TYreturn*TYreturn) as RVTY,
    sum(SPReturn*TYreturn) as CovSPTY,
    count(*) as AntalObs
    From ReturnTotal
    --where datediff(hour, PrevObsTime, ObsTime) < 9
    ) a
    log
    /*
    Select 'SP', count(*) as days, avg(cnt), min(cnt) as mincnt, max(cnt) as maxcnt, sum(cnt) as sumcnt from
    (Select SPDate, count(*) as cnt from SP
    group by SPdate) c
    Union
    Select 'TY', count(*) as days, avg(cnt), min(cnt) as mincnt, max(cnt) as maxcnt, sum(cnt) as sumcnt from
    (Select TYDate, count(*) as cnt from TY
    group by TYdate) c
    Union
    Select 'SPTY', count(*) as days, avg(cnt), min(cnt) as mincnt, max(cnt) as maxcnt, sum(cnt) as sumcnt from
    (Select ObsDate, count(*) as cnt from SPTY
    group by Obsdate) c

    --update SPTY
    --Set ObsDate = convert(varchar, obstime, 112)

    --Select * From SPTY

    */

    Select top 20 *, convert(varchar, obstime, 120), convert(varchar, prevobstime, 120) from ReturnTotal order by obstime desc

    select * from vwSPTY


    ---------------------------------

    - vwspty is the view I created for the purpose.

    - I've highlighted the command I'm trying to pull off currently with red. When trying to process it, I get a "Executing Query" for 20 min +, which didn't happen last time for sure.

    - I don't know if this is enough information for anyone to be able to give a hint at what I might be missing (as you can probably guess, I'm very new at this), but I'd be truly grateful if anyone could give me some directions as to how I can get this working. Thank you very much,

    Martin Falch
    Last edited by MartinFalch; 07-27-09 at 10:07.

  2. #2
    Join Date
    Jul 2009
    Posts
    19
    I might add that executing the code seems to work when adding for instance "Top 10" after the SELECT. However, executing the whole thing takes way longer than I've experienced before. Is this perhaps a Primary Key issue or something like that? I've set ObsTme and ObsDate as primary keys in the SPTY table, same thing for the SP and TY separate tables.

    Is there something that I might be missing that rapidly increases the speed at which the table-creation is executed?

    Thanks in advance

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    Select a.*, b. ...
    into ReturnTotal
    from spty a ,spty b
    where b.obstime=(select max(obstime) 
                     from spty 
                     where obstime<a.obstime
                    )
    First of all, don't write SELECT * statements in production environments. Keep this for quick ad hoc interactive queries.

    This is what your query will return:
    Give me the columns from tabel A and B,
    where B holds the data gathered right before the data in the A-record. There is no other relationship between those two tables A and B.

    In other words: give me the current data (A) together with the preceding data (B). If the table spty holds 100 000 records, you will get 99 999 records in your result set.

    Isn't there some other relationship between A and B? Now you only compare their times.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jul 2009
    Posts
    19
    Hmm well the relationship between the A and B version is that I need to calculate log-returns for the two indexes, SP and TY. In order to do that, I've tried to "lag" the data one period, as you describe - after that, it's possible to directly calculate the log-returns via the unlagged (A) and the lagged (B) observations.

    It worked before, but I must be missing something since the execution takes way to long this time - as mentioned, I get the results I'm interested in if I choose "Top 10" rather than all of the observations, but thing is that in the end I need it for all of the observations - so I need a way for this to be executed without taking 3 days of calculations (or how long it happens to take).

    Thanks alot for your help, it's highly appreciated I'm sorry if some of my explanations don't excactly make sense, I'm afraid I'm rather new to the language.

    - Martin

  5. #5
    Join Date
    Jul 2009
    Posts
    19
    I think perhaps the reason it's going so slow is that I'm trying to run two query's at once; I originally had a View incooperated in the process, which had the contents of the SPTY table pretty much - could this somehow be incooperated in the execution to speed it up dramatically somehow?

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    A view is just an editorial help. It makes writing SQLs faster, it does not make the execution faster. So a view will not solve your performance problem.

    Because of your use of SELECT ... INTO, I guess you have not defined an index on the column spty.obstime.

    I only use SELECT INTO statements for quick interactive queries during development. The same goes for SELECT *. I think you should screen your production code and get rid of both. They are IMHO bad habit.

    Define your table spty explicit (CREATE TABLE dbo.spty(...) ). If you notice performance problems, locate the columns that could be indexed to speed up things. In this case, I would go for spty.obstime.

    By defining your tables explicitly with a create script, you gain a better understanding of what those tables contain, and it also gives you total control to create check or FK constraints, indexes, ...
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jul 2009
    Posts
    19
    Ok thanks alot for the advice, I'll try looking into that

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •