Tag Info

New answers tagged

0

You're seeing the intrinsic drawback of OFFSET pagination. Using LIMIT, you force your DB to sort all the records and count them all the way up to your OFFSET base. Keyset pagination may correspond more closely to your actual needs here and will yield much better performance. As described in the above linked article, simply paginate based on the last key ...


1

You could use inheritance for this. You would have a "master" schema with template tables and only the basic set of columns that all inheriting tables share: CREATE TABLE master.portal(portal_name text); All other tables in various schemata inherit from it, while possibly adding more local columns: CREATE TABLE a.portal(portal_id serial PRIMARY KEY, col2 ...


0

Try this code SELECT users.id AS id, users.name AS name, users.surname AS surname, massages.textAS text, massages.date AS date FROM users left join massages on massages.id = ( SELECT MAX(id) FROM massages as m WHERE (m.recipient='?' AND m.sender = users.id) OR (m.recipient=uesrs.id AND m.sender = '?') ) ...


0

May I recommend that you read enter link description here? It investigates all the possible solutions known to the community until today, and does a SWOT analysis on each option. Best regards, Henrik Staun Poulsen


0

You have used parametrized query, which is the bare minimum expectation to prevent on SQL-Injection. I would suggest some points you can take care of as a validation in C# code I don't see any server side validation on the text you are getting from TextBoxSearch.Text, you can check for some set of character that can be an attempt for SQL Injection. you ...


0

Definitely follow JamesZ's suggestions. Also watch for parameter sniffing amd make sure your statistics are up to date. You said in your post that you have many similar sp_'s I think your first sp_ could get out of control if you started adding more and more vendors/paremeters. Why not make it an sp_ on its own and call it when needed or in a loop? It ...


2

You might speed up the SP by applying index and converting multiple select statements to single as all written for same purpose but as per your below statement you need to do more. 'It is not just this set of queries. I have multiple select and insert statements in my stored procedure; my goal is to take data from different tables to update a row in ...


3

It's quite difficult to give any suggestions based on that information, but here's the approach I normally use for performance tuning. The order of different steps can vary, but these usually give quite a good idea where the improvements should be done. 1) Check for blocking if someone else is blocking the query, you should focus on the blocking query ...


1

As Mark mentioned, UNPIVOT would be a good choice here. I often find it's easier to throw the original query into a temp table then unpivot that. You could use that approach, CTEs or views depending on performance. All the datatypes need to line up for it to work, so I'm casting everything to MONEY in my example, something like this: IF ...


-2

If I were doing this I would code it in Python (or another suitable language) and for each row I read in from the original file write four to the new file. In SQL it would be a case of combining four SELECT statements and then UNIONing them together.


0

PROPOSED QUERY insert into rfdate (date) select selected_date from ( select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, (select 0 t1 ...



Top 50 recent answers are included