I'm new to the database world and have recently started working with a large database with several tables with mainly varchar text and integers. The two largest tables are of ~50 million and 25~million rows. The database contains about 350,000 ID numbers for people and I often need to retrieve information about all individuals that involves joins to both of my very large tables (they're one to many relationships). These queries are also temporal in nature and involve the
between operator to determine events that happen without a certain time frame. It will often take 10-15 minutes for some of these queries to run (I'm still learning and try new indexes to see if I can improve performance. After running out of ram running a particular query I had to my computer froze and I had to reboot. Even after restarting I was unable to detach, drop connections and delete my log files to delete my database (which was in recovery mode).
I booted into safe mode to delete the mdf and ldf files and saw that my log file was twice the size of the data file. If I routinely need to run queries that will return aggregate, temporal information on tables of the above-specified size, is there anything I can do to prevent log bloat? Also, I know SQL Server eats resources for lunch, but what type of specs would a computer need to have to run a query like the following with the table sizes listed above? (it takes an hour on my local machine)
EDIT: this database is static in nature and will not have anything added to it. it is also only unavailable to one user, me. I'm not sure what type of recovery it had, I don't have that PC in front of me at the moment.
--my real database field names are not this generic ;with x as ( --the first CTE finds the number of different classes a person qualified for and lists a number for a given date select rx.pat_id ,c.cal_date ,COUNT(distinct rx.class) as distinctClass from rx,Calendar as c where c.cal_date between rx.fill_date and rx.script_end_date group by rx.pat_id,c.cal_date ),y as ( --the second CTE gives interval number to the date so the max and min date values can be grouped by grp_nbr to give a time span select x.pat_id ,x.cal_date ,c2.id-ROW_NUMBER() over(partition by x.pat_id,x.distinctClass order by x.cal_date) as grp_nbr ,x.distinctClass from x inner join Calendar as c2 on c2.cal_date=x.cal_date ) --this groups the max and min dates with a particular grp_nbr and the number of different classes a person qualifies for select y.pat_id,min(cal_date),max(cal_date),MIN(distinctClass) from y group by y.pat_id,grp_nbr order by MIN(distinctClass) desc