I have these 3 queries - they are the same except each fetches record counts
for one of 3 different record types, nSubsets (type 0), nAssets (type 1) and
nImages (type 2). Is there any way I could get all 3 of these (based on the
Node.Type integer) with a single query?
IF @Error = 0
BEGIN
SELECT @nSubsets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @nodepath + '%'
AND
Node.Type = 0
SET @Error = @ERROR
END
IF @Error = 0
BEGIN
SELECT @nAssets = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @nodepath + '%'
AND
Node.Type = 1
SET @Error = @ERROR
END
IF @Error = 0
BEGIN
SELECT @nImages = COUNT(*)
FROM Node
INNER JOIN Adjacency
ON Adjacency.ID_Node = Node.ID
WHERE Adjacency.Path LIKE @nodepath + '%'
AND
Node.Type = 2
Hi, please excuse me if this seems like a daft or badly formed question. A colleage mentioned that when constructing the where clause in a query that it was important what order the criteria were entered. They weren't sure themsleves but the idea was that starting from the bottom of the query you should add the criteria that will have the effect of reducing the data the most and then work 'up' the query back to the from clause. As a dodgy example, if you have a staff table and you want to run a specific query on all staff over 75 that are male, then:
where gender = "male" and age >- 75 The reasoning being that the age criteria would reduce the size of the data being worked on more than the gender criteria. It's been a while since I've had to look at intermediate tables etc but i'm pretty sure this suggestion will not make any difference to the performance of a query. Thanks,
Hello All, I have a query that I could use some help with. I would like to see how I could make this query more optimised. I am not hte best at Joins so if someone could help me with this that would be great. Right now its taking about 24-30 secs to run which is a no go. Im wondering if some kind of join would work better? thank you much -jes
SELECTrecipes_signed_up_for.user_id, recipes_signed_up_for.recipe_id, recipes_signed_up_for.use_in_lesson_plan AS use_in_lesson_plan, recipes_signed_up_for.attached_lesson_plan AS attached_lesson_plan, recipes_signed_up_for.participate, recipes_signed_up_for.authorized AS authorized, recipes_signed_up_for.date_signed_up_for AS date_signed_up_for, users.user_id, users.f_name AS f_name, users.l_name AS l_name, users.email_address AS email_address, users.primary_phone AS primary_phone, recipes.recipe_name AS recipe_name, recipes.recipe_id AS recipe_id, recipes.number_served AS number_served, recipes.last_day_to_sign_up_for AS last_day_to_sign_up_for, recipes.recipe_instructions AS recipe_instructions, recipes.active_recipe, recipe_ingredients.recipe_ingredient_id, recipe_ingredients.recipe_id, recipe_ingredients.ingredient_id, recipe_ingredients.ingredient_quantity AS ingredient_quantity, recipe_ingredients.ingredient_unit, recipe_ingredients.active_ingredient AS active_ingredient, ingredients.ingredient_id, ingredients.ingredient AS ingredient_name, recipes_signed_up_for_ingredients_needed.ingredient_id, recipes_signed_up_for_ingredients_needed.ingredient_needed AS ingredient_needed FROMrecipes, recipe_ingredients, ingredients, recipes_signed_up_for_ingredients_needed, recipes_signed_up_for, users, locations, regions WHERErecipes.recipe_id = recipe_ingredients.recipe_id AND recipe_ingredients.ingredient_id = ingredients.ingredient_id AND recipes_signed_up_for_ingredients_needed.user_id = #url.user_id# AND recipes_signed_up_for_ingredients_needed.ingredient_id = recipe_ingredients.ingredient_id AND recipes_signed_up_for_ingredients_needed.ingredient_needed = 1 AND recipes.recipe_id = recipes_signed_up_for.recipe_id AND recipes_signed_up_for.user_id = users.user_id AND recipes_signed_up_for.user_id = #URL.user_id# AND recipes_signed_up_for.participate = 1 AND locations.region_id = regions.region_id AND recipes_signed_up_for.recipe_id IN (SELECTrecipe_id FROMrecipes WHEREactive_recipe = 1) ORDER BY recipes.recipe_name
Hi guysIs there any way I can run this query faster? Should I take out the ORDER BYclause? This is supposed to return 17,000 rows and takes around 30 minutes orso. Is there no way at all to get this result faster?select r.AttorneyName, r.sitename, r.applicationid, r.clientsurname, r.clientinitials, r.clientidno, r.grantedamount, r.bankname,r.accountnumber, r.status, r.grantdate, r.consultantname, r.propertyaddress,r.erfdescription, r.commenthistory, br.expectedregdatefrom bondtrak..rptdetail rjoin ebondprd..bankresponse bron br.applicationid = r.applicationidwherer.rundate = '20051010'and r.primarybankind = 'Y'and r.status = 'granted'and r.statusdate between '20020101' and '20050930'and r.businessunit in ('bond choice', 'ppl')order by r.sitename, r.consultantname, r.statusdateThanks for any help.Driesen--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
hi, I want to know how to optimise this query, the results almost reach 6000 rows, I want to speed this query.
condition2=Select Distinct A,B,B+C+D,E,F,G From a.table where condition1 order by A,B,B+C+D,E,F,G Select Sum(amount) From b.table where condition2 And date=datetime.
I use this piece of code in a trigger to check if new entered row has unique order number in the given year:
IF EXISTS
(
SELECT COUNT(*)
FROM SPD_Orders
GROUP BY ORD_Year, ORD_Number
HAVING COUNT(*) <> 1
)
RAISERROR('Order with this No exists in given year, 11, 1)
but this code causes deadlocks in my database.
Any suggestions how can I change it to avoid these deadlock? 1. Some constrain? If yes, so how to write it? 2. Index like to enable query optimiser to change execution plan?
CREATE NONCLUSTERED INDEX [_dta_index_SPD_Orders_10_1429580131__K8_K7] ON [dbo].[SPD_Orders]
(
[ORD_Year] ASC,
[ORD_Number] ASC
3. Same index as above but unique and then I will not have to use this trigger, cause database itself with return an error? 4. Any other, the best solution ?
Ok I have the following SQL, I have a subquery in the SELECT part but also the same subquery in the WHERE part as well.What I'm trying to do is get all parents that have children OR get all parents with no children OR just get all parents regardless (@HasResponses is a BIT that can be 1, 0 or null). At the same time I want to count the total number of children in the select list, but I'm having to copy the same subquery in the SELECT and WHERE parts which doesn't seem terribly optimal to me (maybe it is, that's why I'm asking). I've tried referencing the column alias in the select list (AS [Responses]) for the where part (@HasResponses = 0 AND [Responses] = 0), but it doesn't seem to work.Is this the most optimal way to do it? Is there a better way? I'm working with SQL Server 2005.SELECTf.FeedbackText AS [Feedback Comment],u.Name AS [Feedback Author],f.CreatedDate AS [Created On],(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) AS [Responses]FROM Feedback fINNER JOIN [User] u ON f.StaffID = u.StaffIDWHERE f.CreatedDate >= @DateFromAND f.CreatedDate <= @DateToAND(@HasResponses IS NULLOR(@HasResponses = 1 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) > 0)OR(@HasResponses = 0 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) = 0))
I have created an database with 3 indexed (not clustered) fields key : bytes 100 error : integer status : bytes 6 I would like to know how to import 3 million or more records quickly. If I don't index the the fields, the operation takes 3 hours. With indexed fields it takes overnight. Therefore, how can i optomise the import operation from a .txt file.
Have built tables with stitistical data (very large)in SQL Server 2000
All with 2 fields in common: "Node" and "Datestamp" At the moment none is indexed.
Tables will have joins added in Business Objects Universe. How do I know if adding indicis would improve speed of queries? And if so how are these best added? Do all tables need indicis or just some? What does it mean to Optimise the db above adding Index columns? Can the DB be made optimise itself automatically?
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
Hi, I am using visual web developer2005 express edition and finding hard time to get my query run in this i am making my own login page as i have few more things to ask to user before they get logged in so i am not using the login control. i want to write my own query without help of sqlDataSource control from start something like sqldatasource con=new sqldatasource; con.connection String="" then what all things will come........ ??? and please give me some poitners to some articles which help one to do the requested.
I have an update query which either inserts a row or increases quantity, depending if row exists or not. It works, better than my explanation probably.After that query could be a good time to count total of all calculated sub sums.Something like this. previous queryEND goSELECT SUM(SubTotal)FROM dbo.t_Shoppings I have tried this on the tool which has a long name, but I think my way didn't work. (Microsoft sql server management studio express)Is this possible or do I have make and call another stored procedure.I can send my sp if someone wants.
2 queries together, in a single stored procedure Select top 20 * from Product where Active=1select Count( *) from product if i execute such one how can i get the 2 results in vb/c# ?
Not sure if I've got the correct place for this question. But, I'm trying to create and sql query to list the lates 10 items in a database. So far I haven't had any luck finding this.
All I have is a normal query (below). Can anyone help me please?
SELECT * FROM pages WHERE show = 'yes' ORDER BY id Desc;
Hi,I have 5 tables in sql database, naming Book, Category, Subject, UserDownload, User.In Book table, BookID, BookTitle, CategoryIDIn Category table, CategoryID, CategoryNameIn Subject table, SubjectID, SubjectName, CategoryIDIn UserDownload table, UserID, BookIDIn User table, UserID, UserNameI used Book to store information of books. Those books has many categories. In those categories, there is also some subjects.When user downloads book, I update UserDownload table.The result I want to get is, Top Ten Download Subject. How can I get? Please help me.
I just upsized my Access2K db to SQL. I am using Front Page 2000 for my website. When I had the database as Access, I was able to use one of my Access queries as my record source for my data base. I was able to choose between my queries AND my tables as the source for my records. Now that I've upsized, I am no longer given that choice. My only choices are the tables. Unfortunately, my database is designed to pull records from a query, not just a table. So my question is, in FP2000, how do I use a QUERY from my newly upsized SQL db as my record source?
I have a query that requires multiple joins and has several one-to-many relationships with other tables. The basic query I have is this:
Code:
SELECT TOP 50 eventID,eventTitle,eventAbstract,eventInsertDate,eventUpdateDate,eventAuthID_fk, eo.eventOccurrenceID,eo.eoDescription,eo.eoApprovedDate, l.LocationID,l.LocationShortName,c.categoryID,c.categoryName,a.ageID,a.ageDescription FROM enm_eventOccurrence eo INNER JOIN enm_events e ON e.eventID=eo.eoEventID_fk LEFT JOIN (enm_categories_occurrence co LEFT JOIN enm_categories c ON c.categoryID=co.co_categoryID_fk) ON eo.eventOccurrenceID=co.co_eventOccurrenceID_fk LEFT JOIN (enm_location_occurrence lo LEFT JOIN location l ON lo.lo_locationID_fk=l.LocationID) ON lo.lo_eventOccurrenceID_fk=eo.eventOccurrenceID LEFT JOIN (enm_ages_occurrence ao LEFT JOIN enm_ages a ON ao.ao_ageID_fk=a.ageID) ON ao.ao_eventOccurrenceID_fk=eo.eventOccurrenceID
This works, but it will pull out multiple rows for the same eventOccurrenceID (the PK for eventOccurrence) because of the several one-to-many relationships. For instance, there could be multiple categories, locations and ages for one event occurrence, so this will produce multiple rows. But, since I need to get only a limited of rows, the TOP will fail since there are multiple rows for each, and there's no way to tell how many rows exactly will come out (so just multiplying the TOP value is out of the question). Using DISTINCT or GROUP BY also seems to be out of the question, since I need to have the fields from the one-to-many tables in the SELECT list but cannot GROUP BY them.
My proposed solution is to run a query selecting only the eventOccurrenceID and using the WHERE information that the user is requesting, then running another query (with the full select list) with a "WHERE eventOccurrenceID IN (". But, I hate using two queries and just wonder if there's a better way around this.
I frequently need to run ad hoc queries against a database, capturing the output in a text file. In SQL Server 2000 I used Query Analyzer with no problems. In 2005 I'm trying to use the query function in Management Studio-but the output is (so far) unusable due to a Null character (hex 00) that follows every 'real' character. I assume this is due to Unicode but haven't found any way (yet) to tell it to just output in plain everyday text. (ASCII or ANSI-either will work.)
Is there some other tool I can use for SQL queries?
1. name, surname of the plasma donators(type='plasma'), with Sex='F' and younger than 45 years old.
My solution: SELECT d.Name, d.Surname FROM Donators d, Filles f WHERE d.Id=d.IdFile AND f.type='plasma' AND d.sex='F' AND d.age<45
2. average age of the plasma donators
SELECT AVG(d.age) FROM Donators d, Files f WHERE f.type='plasma'
3. the states which have more donators
I have no idea how to do it
4. the id's of the plasma donators from Chicago
SELECT d.id FROM Donators d, Files f, City c WHERE d.id=f.idfile AND d.idcity=c.idcity AND f.type='plasma' AND c.name='Chicago'
5. the id's of the cities without plasma donators
SELECT c.idcity FROM City c WHERE NOT IN (SELECT c.idcity FROM City c, Donators d, Files f WHERE f.idfile=d.id AND f.type='plasma' AND d.idcity=c.idcity)
6. remove the donators older than 80, after having removed the corresponding files
No idea how to do it
In conclusion, could you tell me if my queries are correct and help me with the others?
Table 'dbo.tblonenet_sdt_proj_hold' Schema as follows:
cust_id int dateon DATETIME dateoff DATETIME
There are two possibilites
1) Its no longer on hold and so a dateon and dateoff value will be in the table.
2) Its still on hold and so dateoff will be NULL.
I want to add the total time its been on hold between the two dates and there could be multiple entires for cust_id 802
SELECT Hon,Hoff FROM
(SELECT sum(dbo.GetBusinessdays(dbo.tblonenet_sdt_proj_hol d.dateon,getdate())) FROM dbo.tblonenet_sdt_proj_hold WHERE cust_id='802' AND dateoff IS NULL) AS HON
[code]...
The resulting error in SQL Management Studio 2005 is 'No column was specified for column 1 of 'HON'.'
I am creating a interaction application for alton towers available through a website (using asp.net). This website is connected to a database(sql server 2000) containing infomation on different shops, rides, restaurants and facilities at the park.
I have inputted data to my database. The user should be able to do searches such as: rides (and shops or all) available in a certain area. rides in a certain area above a certain height rescrictions. etc
I'm confused. I have used SQL statements before and I know it is possible to retrieve this information from the database, but how should I go about this and how should I be storing this information so that I am able to call it from the front teir.
I have read up on views, stored procedures and triggers and I'm a bit lost. Should I be creating all the possible queries and then store them as a view (or stored procedure) I thought I would just have to write a sql statement but it seems a lot more confusing...
and what about triggers and user defined statements??
how do i make a querie that finds relations to a "part number" for models, and assemblies, but when it finds a relation of part number with a Product, it gives additional attributes for information? this code that i have so far doesn't do waht i want,, showing
Label(LabelID,Label) labelid=pk ------------------------------- Shelf(ShelfID,Shelf) shelfid=pk ------------------------------- Country(CountryID,Country) countryid=pk --------------------------------------- Track(TrackID,Track) trackid=pk ------------------------------- Artist(ArtistID,Artist,ArtistPhoto,CountryID) artistid=pk,countryid=fk country(countryid) ----------------------------------------------------------------------------------------- CD(CDID,CDTitle,CDDate,CDCover,LabelID,ShelfID) cdid=pk, labelid=fk label(labelid),shelfid=fk shelf(shelfid) ------------------------------------------------------------------------------------------------------------ CDTrack(TrackID,CDID) trackid,cdid=pk, trackid=fk track(trackid), cdid=fk CD(cdid) ---------------------------------------------------------------------------------- ArtistTrack(ArtistID,TrackID) artistid,trackid=pk, artistid=fk artist(artistid),trackid=fk track(trackid) --------------------------------------------------------------------------------------------------------- I've already created these tables but i need some aid writing these queries coz i am still a beginner in sql
Search Queries ------------------------------------------------------------ Input Artist OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 1 Group ByCDSection ------------------------------------------------------------ InputCDTitle OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 2 Group ByCDSection ------------------------------------------------------------ InputTrack OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 3 Group ByCDSection ------------------------------------------------------------ InputLabel OutputCDTitle,CDDate,CDCover,Label,CDSection,Shelf 4 Group ByCDSection ------------------------------------------------------------
Ashish Amodia writes "how to create tables in ms-sql? how different opration can be perform in database table, like insert, update, delete with tables. how to create store procedure?"
Iam developing a randomizing program .which selects random rows frm the database.iam using sql as backend and asp.net as front end. i allow user to give a number say 1000, so 1000 random rows will be slected frm the database.iam using NEWID() for random selection. But how do i put the user value into query. so as to select that many random rows from the databse. please help me out on this
i hav this problem .i hav one column in my table which contains records which can be integers or string. i allow user in my .aspx page to enter integers in three text boxes. and depending on those integers say 1,2,3 ... i hav to select those rows from my table. where the problem is that i hav to selct the row which is say row no.1 which can contain string or integer and iam not getting the slected result instead some integers . the query iam using is SELECT " & range1 & " UserID FROM TAble
where range1 is the integer that user puts into textbox .i hav only one cloumn(UserID) in my table which can contain integer or string so i hav to select the row which is what the user has entered but contains string or integer(which is not what user has entered).
Can we say that if there are 4 tables that are joined , the query will definately take a longer time to execute, can we still reduce the execution time
I mean how can u still further optimize the performance , or say that the performace can no further be done on the particular job