I have a SQL Tabe "Document" which contains a lot of rows (up to a few millions).
When I'm executing an Select-Statement it takes about 0.5seconds.
But when I'm executing an Update with the very same WHERE-clause it takes about 20 to 50 seconds, depending on the amount of affected rows.
Here a...
I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something.
I have the following package
create or replace package tq84_pkg_c as
procedure proc_1;
end tq84_pkg_c;
/
with its body
create or replace package body tq84_pkg_c as
...
SELECT
COUNT() as posts,
FROM users
LEFT JOIN threads on threads.user_id=users.user_id
LEFT JOIN posts on posts.user_id=users.user_id
WHERE user_id='1'
When working with SQL Server, there can be multiple application host accessing it, and each application can have one or many connection. Each connection can potentially have multiple transaction (please correct me if I am wrong). Each transaction can perform query or non-query SQL.
In my experie...
My result is this:
Sitecode Month Amount
-------- ----- ------
XX Jan 1000
XX Jan 3000
XX Apr 3000
XX Apr 1000
Result will show like this:
Sitecode MonthJAN MonthAPR
-------- -------- --------
XX 4000 4000
I think this will do what you want. This implements the UNPIVOT function and then get the column information from the information_schema.columns view to get the result:
select product_id,
product_category,
parent_id,
delivery_in_days,
PROD_EXTENSION_NAME,
case when c.data_type = 'varc...
What does Reputation do?
As a registered user, your reputation on the site is a part of your identity on the site. It reflects, to an extent, your familiarity with the site, the amount of subject matter expertise you have and the level of respect your peers have for you. It can generally only be...
Yesh, I know what you mean. I'd like to have more rep on DBA, but that means that either I start answering some questions, or post interesting questions. For the answering part, most of the questions there are just over my head....I could start posting some questions, though
This seems like a pretty simple question, but it is giving me trouble. Consider
corrmodel=# SELECT pvalue, margstat, datafile_id, index from data limit 1; pvalue | margstat | datafile_id | index --------+----------+-------------+------- 0 | 58.5602 | 58 | 448
I want to group together all rows with the same datafile_id and index, but want these groups ordered by pvalue desc and margstat (in that order). Is this as trivial as it sounds?
The guy with the massive logs dumped into questions (ram was it?) has been mod-messaged .. sigh. Let's see if he goes back and tries to clean up the Qs
SELECT A.*
FROM data A
INNER JOIN (SELECT datafile_id, index, AVG(pvalue) pvalue, AVG(margstat) margstat
FROM data
GROUP BY datafile_id, index) B
ON A.datafile_id = B.datafile_id AND A.index = B.index
ORDER BY B.pvalue DESC, B.margstat DESC, A.datafile_id, A.index, A.pvalue DESC, A.margstat
@Lamak which means someone else is likely to find it hard to explain. So if we can get some good sampling of text there, we might can help the next googler :D
@AaronBertrand just starting on your post ... "Usually this database does not need to be in full recovery, especially since if you are in disaster recovery mode and restoring your database, the last thing you should be worrying about is trying to maintain sessions for users in your web app"
I've filled out the paperwork 2 or 3 times, printed it and taken it to the post office but I either pick the days they aren't processing applications or I miss the time frame (subset of their hours) or I forget it's a federal holiday
Ok. I didn't know this forum was only for geniuses to clear their doubts. I don't have much experience in sql and couldn't find any related solution after many hours of googling. So instead of pointing the flaws in the question and try to help me out, it will be really useful for me and whoever comes in future with this kind of an issue. Thanks. — jijo3 mins ago
@bluefeet I've seen it before. But in this case, the question is kinda interesting, and would need a really complex answer. I don't believe that just cause he didn't tried something then we should close it
I have a table say EMPLOYEE and it has a column EMPLOYEE_NAME.
I want to see the phrases which comes in more than 5 records.
The phrase can be anything of 3 characters minimum.
For example, my table looks like this
EMPLOYEE_NAME
User1
User2
User3
Client1
Client2
Client3
Use1
Aent1
Auser2
ent3
...
@AaronBertrand nice. But this is valid for the first chars only, the user wants the same for every possible string of more than 3 chars, not only in the beggining
@AaronBertrand that works if you know the string ahead, but it looks like from the sample that they want some with the start of the string and others with end.
Some times our users tend to get a little full of themselves and take a very high and mighty attitude. I think it's completely valid to ask a question if you've done your homework and still aren't sure how to do something.
select substring(EMPLOYEE_Name,Number, 3)
from EMPLOYEE
cross join Numbers
where number.number between 1 and 7 and
len(substring(EMPLOYEE_Name,Number, 3)) = 3
group by substring(EMPLOYEE_Name,Number, 3)
having count(*) >= 5
@Zane yeah, and I've seen a lot of very simple questions with something like "I've tried many things but none worked" that get 3 pr 4 answers in a minute. With no-one thinking that it's not valid question.
@Lamak i think your upvoted comment summarizes it best. sometimes the barrier to entry for "trying" is "starting." give 'em a good place to start and they may just try a bunch of wacky crap
then we'll have to deal with things like "no you don't need a cursor to loop through the data to find the MAX(editdate)" but oh well, right?
@swasheck are you telling my I shouldn't have a cursor that cycles through a 500 million row sales table to find all records where someone put extra cheese on their hamburger? :)
CREATE PROCEDURE MyProc( @ETLLoadDate DATETIME ) AS BEGIN DECLARE @DimCal Int = (SELECT DimCalendarID FROM DimCalendar WHERE CalendarDate = @ETLLoadDate) END
@Zane the plan for the query is what's getting botched, doesn't matter if you remove the procedure container, it works now but you will cache a plan and someone else will use a bad parameter later, and you're back at square one.
I would add OPTION (RECOMPILE) to the query. A little cleaner than the parameter switch-a-roo (and a lot cleaner than dynamic SQL), and a little costlier on every run, but you get predictably slightly worse performance than sometimes great and other times really sucky.
the parameter passed to the SPROC is @ETLLoadDate and that portion runs the Query to give me the dimcalendarID which i use to sift through my fact tables.
the actual query is more like Select Stuff From FactThings Where DimBusinessDate >= @DimCal Since dimCal isn't the parameter passed by the sproc but is a declared variable shouldn't that prevent parameter sniffing?
I was just asking from a conceptual stand point if portion where the sproc parameter is passed loads instantly to feed a declared variable if it would still sniff for the declared variable.
No need for a Question I will solve this damn problem
@Zane you have. i've had very little exposure to Muse before this one --- just what hits the radio. I decided to listen to this one. I started off hating it but some of the songs have grown on me
@billinkc @jcolebrand maybe they renamed the configs in newer versions
@MikeFal I had rented a car when I went to the StL SQL Saturday. It was perfect, had a bluetooth connector so I could stream anything to the car stereo. I have seen the future and it was the car 11 years younger than mine
@swasheck completely is...i think if i worked from an office now, i would bring my lunch from home...haven't worked in an office since I've been married
@swasheck i'm about to give streaming netflix the boot because i decided not to pay for xbox gold anymore...that is unless I can get my old linksys router to work as a wireless bridge to connect my bluray player to.
When I'm not listening to internet radio stations (Tunein Radio Pro), I love google play. Took a while but it uploaded all my music so I have physical and cloud media wherever I wish
I have a SQL Tabe "Document" which contains a lot of rows (up to a few millions).
When I'm executing an Select-Statement it takes about 0.5seconds.
But when I'm executing an Update with the very same WHERE-clause it takes about 20 to 50 seconds, depending on the amount of affected rows.
Here a...
I believe I stumbled upon a bug with PL/Scope in combination with associative arrays, but perhaps I overlook something.
I have the following package
create or replace package tq84_pkg_c as
procedure proc_1;
end tq84_pkg_c;
/
with its body
create or replace package body tq84_pkg_c as
...
SELECT
COUNT() as posts,
FROM users
LEFT JOIN threads on threads.user_id=users.user_id
LEFT JOIN posts on posts.user_id=users.user_id
WHERE user_id='1'
My result is this:
Sitecode Month Amount
-------- ----- ------
XX Jan 1000
XX Jan 3000
XX Apr 3000
XX Apr 1000
Result will show like this:
Sitecode MonthJAN MonthAPR
-------- -------- --------
XX 4000 4000
I think this will do what you want. This implements the UNPIVOT function and then get the column information from the information_schema.columns view to get the result:
select product_id,
product_category,
parent_id,
delivery_in_days,
PROD_EXTENSION_NAME,
case when c.data_type = 'varc...
I have a table say EMPLOYEE and it has a column EMPLOYEE_NAME.
I want to see the phrases which comes in more than 5 records.
The phrase can be anything of 3 characters minimum.
For example, my table looks like this
EMPLOYEE_NAME
User1
User2
User3
Client1
Client2
Client3
Use1
Aent1
Auser2
ent3
...
i have a table with 3 columns
Name department salary
now how can i determine using one query to find 3rd highest salary in each department ?
